Code for "Refresh All" and inputting system time as excel file name

[Xanadin]

Hi everyone,

I came to know about autoit yesterday and was trying to make an autoit file but I could not figure out many things. I would be grateful if any of the expert coders spend 5 minutes for me and write the codes for me.

I want to create an autoit file which will do the following functions:

1. Open an Excel file named "Test1.xls" located in the same folder as the .au3 file.

2. Click the "Refresh All" button of the excel file.

3. Wait for 3 minutes.

4. Save the file (in the same folder) with the system time as the file name. For example, if the system time is 02:30 pm then the file should be saved as "1430.xls".

5. Do the same thing from serial number 2 to 4 until it is 03:00 pm system time.

6. Close the excel file.

It would take about 5 minutes for an expert coder but it would save me 20 to 30 hours of time!

Please help!

[smartee]

Welcome to the forums

if any of the expert coders spend 5 minutes for me and write the codes for me.

But then we would have only fed you for a day

Look in the help-file for the functions whose names start with _Excel, and try coding something, when you encounter difficulty post your code, and we will help you.

[sleepydvdr]

What you want is extremely easy to make. The only thing that could trip you up for hours would be the two lines below, so I'll give you them. You will also want to look up the commands Send and Sleep. Like Smartee said, if you have trouble, post what you wrote and we will help.

WinActivate("[CLASS:XLMAIN]")

And

WinWaitActive("[CLASS:XLMAIN]")

Edited August 2, 2011 by sleepydvdr

[Xanadin]

What you want is extremely easy to make.

Thank you for your reply. For you, this may be extremely easy, but not for a novice like me! All I could wrote is given below, but only the open and close excel file is working properly, other functions are not working.

#include <Excel.au3>
#include <Date.au3>

$sFilePath1 = @ScriptDir & "\Test1.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)

WinActivate("[CLASS:XLMAIN]") 

WinWaitActive("[CLASS:XLMAIN]") 

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Do
; This will select External Data and Refresh All
;WinMenuSelectItem ( "title", "text", "item" [, "item" [, "item" [, "item" [, "item" [, "item" [, "item"]]]]]] )
WinMenuSelectItem("[CLASS:XLMAIN] ", "", "Refresh All")

Sleep(60000)

; Get system times
$aTime = _Date_Time_GetSystemTimes()

_ExcelBookSaveAs($oExcel, @ScriptDir & "/$aTime)", "xls")
If Not @error Then MsgBox(0, "Success", "File was Saved!", 3)

Until $aTime = 15:00

_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of 

changes

[sleepydvdr]

For a beginner, you are learning quite fast. I made a couple changes with some notes:

#include <Excel.au3>
#include <Date.au3>

$sFilePath1 = @ScriptDir & "\Test1.xls" 
$oExcel = _ExcelBookOpen($sFilePath1)

WinActivate("[CLASS:XLMAIN]") 

WinWaitActive("[CLASS:XLMAIN]") 

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Do

Send ("{F9}") ; <-- F9 refreshes the entire file

Sleep(60000)

$newFileName = @HOUR & @MIN & ".xls" ; Easier way to create the file name - also, the other way uses colons which aren't allowed in file names

_ExcelBookSaveAs($oExcel, @ScriptDir & "\" & $newFileName)
If Not @error Then MsgBox(0, "Success", "File was Saved!", 3)

Until @HOUR = 15 and @MIN = 0

_ExcelBookClose($oExcel, 1, 0)

[Xanadin]

For a beginner, you are learning quite fast. I made a couple changes with some notes:

Thanks a Million for your great help. Everything is working fine Except "Refresh All". This "Refresh All" button is actually located on the "External Data" menu bar. When I click on this button it refreshes the data that is being grabbed from an External Web Query. But manually pressing the F9 key does not refresh this data, so it is not working in the code. Is there a way to write a code to click "Refresh All" by using the WinMenuSelectItem function?

Again, thanks a LOT for your time.

[meatsack]

I am trying to work with Refresh data procedure also.

[Juvigy]

In the DATA tab of excel there is an REFRESH ALL button is manually pressing that refreshing the data?

This "external data" tab seems like an add-on or similar.It will be difficult to help whithout more details.

[Xenobiologist]

Did you try to send the keys which are necessary to click the button to the window?

[JoHanatCent]

$oExcel.ActiveWorkbook.RefreshAll

[Spiff59]

You might also want to change:

Do
Until @HOUR = 15 and @MIN = 0

into just:

Do
Until @HOUR = 15

With a Sleep(60000) built into your loop, it's certainly possible that an iteration of your loop could start at 14:59 and not end until 15:01, in whch case your script would run for at least another 24 hours.

Edit: grr... fix codetags...

Edited September 29, 2011 by Spiff59