How to automate the following tasks?

[LunaMoon]

Dear all,

I am very newbie to Windows scripting, but I really need your help.

Could you please help with the following automation task?

1. Open up an Excel 2003 worksheet A;
2. Click a button in A, wait for a while, and save it;
3. Open up another Excel 2003 worksheet B; must be opened in the same
process.
4. Press F9 to recalculate/refresh this sheet B.
5. Save the file B.

I need to run this whole procedure every morning at 3am...

If I don't find a solution, I have to do that myself manually at
3am...

Thank you for your help!

[Pegasus [MVP]]

Post by LunaMoon
Dear all,
I am very newbie to Windows scripting, but I really need your help.
Could you please help with the following automation task?
1. Open up an Excel 2003 worksheet A;
2. Click a button in A, wait for a while, and save it;
3. Open up another Excel 2003 worksheet B; must be opened in the same
process.
4. Press F9 to recalculate/refresh this sheet B.
5. Save the file B.
I need to run this whole procedure every morning at 3am...
If I don't find a solution, I have to do that myself manually at
3am...
Thank you for your help!\

You are obviously steering in the direction of macros. The downloadable
helpfile Script56.chm gives you full details on theSendKeys method. I would
call this the quick and dirty way: It is quick and easy to program but, like
all macros, it is unreliable because it can (and will!) be tripped up by
numerous events which you can neither anticipate nor control. You will be
forced to rerun your 3am macro regularly at 8am when finding that it has
failed again.

A far better way would be to write a proper script based on the
Excel.Application object. It can do everything you want it to do, in
particular the functions that are currently behind your button press in
spreadsheet A, and it will be totally stable and robust. Unfortunately it
will require a lot more programming effort than just knocking together a few
SendKeys statements.

[urkec]

Post by LunaMoon
Dear all,
I am very newbie to Windows scripting, but I really need your help.
Could you please help with the following automation task?
1. Open up an Excel 2003 worksheet A;
2. Click a button in A, wait for a while, and save it;
3. Open up another Excel 2003 worksheet B; must be opened in the same
process.
4. Press F9 to recalculate/refresh this sheet B.
5. Save the file B.
I need to run this whole procedure every morning at 3am...
If I don't find a solution, I have to do that myself manually at
3am...
Thank you for your help!
.

Here is a simple script to open an Excel workbook, execute a macro
associated with a button located in its first sheet, save and close the
workbook. The script then opens another workbook and callculates all formulas
in its first sheet:




Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objWorkbookA = _
objExcel.Workbooks.Open ("C:\scripts\Book1.xls")

If Not objWorkbookA.Sheets(1).Shapes("Button1") Is Nothing Then

objExcel.Run objWorkbookA.Sheets(1).Shapes("Button1").OnAction
objWorkbookA.Save
objWorkbookA.Close

Set objWorkbookB = _
objExcel.Workbooks.Open ("C:\scripts\Book2.xls")
objWorkbookB.Sheets(1).Calculate
objWorkbookB.Save
objWorkbookB.Close

End If

objExcel.Quit

[Matija Kapraljevic [Revenger]]

Post by LunaMoon
1. Open up an Excel 2003 worksheet A;
2. Click a button in A, wait for a while, and save it;
3. Open up another Excel 2003 worksheet B; must be opened in the same
process.
4. Press F9 to recalculate/refresh this sheet B.
5. Save the file B.

Beside other suggestions, you could write the whole thing in Excel VBA and
place the code into Workbook_Open() event handler so it would execute every
time this workbook is opened.

Post by LunaMoon
I need to run this whole procedure every morning at 3am...

Then you would only need to setup a batch file or a VBScript file to open
(execute) that workbook and schedule it through Scheduled tasks to run
every day at 3 am.