Excel Data Connections are not refreshed opening with Autoit

[barnabeck]

Opening my Excel file through Autoit doesn't cause the data connections to be refreshed, which would be the case if I manually open the file. I tried to find a solution but after one day of struggling I changed strategy: Now I let Window's Task scheduler open the file, which then is updated correctly. My idea then was to schedule the compiled AutoIt script 5 minutes later in order to do the operations on that file. But this doesn't work, as I probably do not address well AutoIt to the by then already opened excel file.

Very strange: if I manually open the excel file and then launch the script (or manually or scheduled) all operations are executed correctly. If I launch the excel spreadsheet with the task scheduler this is not working.

I guess I'm missing something. The most logic way would be to do everything in one script but then I have to force the data connection to be updated on the opening of that file...

Anybody has a hint or an example?

Martin

[JohnOne]

It's customary to post the code that you are having an issue with.

help will arrive much quicker if there is code to test.

[water]

Welcome to AutoIt and the forum!

Do you use the Excel UDF?

[barnabeck]

I would have added the code, but I was already at my home place with no access to that script and somehow hoped to find a hint to work on by the time I would start my day here in Europe... now most likely I have to wait for you to be operative again; anyway, here is what I did on in my first AutoIt script:

#include <Excel.au3>

Local $oAppl = _Excel_Open()

Local $sWorkbook = @ScriptDir & "\Infoboard\infoboard.xlsx"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

Sleep(60000)

_Excel_RangeInsert($oWorkbook.Worksheets('Log'), "3:3", $xlShiftDown, $xlFormatFromRightOrBelow)
Local $Dia = _Excel_RangeRead($oWorkbook, $oWorkbook.Worksheets('Log'), "M2", 3)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets('Log'), $Dia, "A3")
Local $Hora = _Excel_RangeRead($oWorkbook, $oWorkbook.Worksheets('Log'), "N2", 3)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets('Log'), $Hora, "B3")

Local $YesNo = _Excel_RangeRead($oWorkbook, $oWorkbook.Worksheets('Log'), "K2", 3)
If $YesNo = 'No' Then
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets('Log'), "Updated & Printed", "C3")
   _Excel_Print($oAppl, $oWorkbook)
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets('Log'), $Dia, "F2")
Else
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets('Log'), "No Update available", "C3")
 EndIf
_Excel_BookSave($oWorkbook)
_Excel_Close($oAppl)

Martin

[water]

Does

$oWorkbook.RefreshAll

work?

[barnabeck]

Sorry, I am very new and just put the code together from examples I found....

... so where am I supposed to place $oWorkbook.RefreshAll?

it has to be refreshed on the onening so I would guess:

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook.RefreshAll, Default, Default, True)

Martin

[water]

Try:

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)
$oWorkBook.RefreshAll

[barnabeck]

YES!!

That does the job... thank you very much.

Does the script waits until the RefreshAll is completed or do I need to place a sleep() command in order to make sure that enough time has passed so that the data connection has been updated?

Martin

[water]

According to MSDN it seems to depend on the object being refreshed.

"Objects that have the BackgroundQuery property set to True are refreshed in the background."