barnabeck

Excel Data Connections are not refreshed opening with Autoit

9 posts in this topic

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

Share this post


Link to post
Share on other sites



Welcome to AutoIt and the forum!

Do you use the Excel UDF?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Does

$oWorkbook.RefreshAll

work?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Try:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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."


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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