barnabeck Posted April 8, 2015 Share Posted April 8, 2015 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 Link to comment Share on other sites More sharing options...
JohnOne Posted April 8, 2015 Share Posted April 8, 2015 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. AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans. Link to comment Share on other sites More sharing options...
water Posted April 9, 2015 Share Posted April 9, 2015 Welcome to AutoIt and the forum! Do you use the Excel UDF? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
barnabeck Posted April 9, 2015 Author Share Posted April 9, 2015 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 Link to comment Share on other sites More sharing options...
water Posted April 9, 2015 Share Posted April 9, 2015 Does $oWorkbook.RefreshAll work? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
barnabeck Posted April 9, 2015 Author Share Posted April 9, 2015 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 Link to comment Share on other sites More sharing options...
water Posted April 9, 2015 Share Posted April 9, 2015 Try: Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) $oWorkBook.RefreshAll My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
barnabeck Posted April 9, 2015 Author Share Posted April 9, 2015 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 Link to comment Share on other sites More sharing options...
water Posted April 9, 2015 Share Posted April 9, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now