Sign in to follow this  
Followers 0
Xanadin

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

11 posts in this topic

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!

Share this post


Link to post
Share on other sites



Welcome to the forums :)

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

:D 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. ;)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by sleepydvdr

#include <ByteMe.au3>

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

#include <ByteMe.au3>

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

I am trying to work with Refresh data procedure also.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

$oExcel.ActiveWorkbook.RefreshAll


Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0