Ytaj Posted July 15, 2014 Share Posted July 15, 2014 (edited) Hello, I am completely new to AutoIt (or coding in general) and would like to ask you for some help. I have 6 files that are dependent on each other - 4 excel worksheets and 2 mathcad. What I want to do following: 1 Open excel file (on open excel should automatically update all cells with data extracted from another program) 2 Wait a few seconds so excel updates all 3 Save and exit 4 Open Mathcad file (here are some calculations that are being made using data from excel sheet from step 1 - again it will update automatically) 5 Save and exit And so on... So the problem should be simple enough - open, wait, save and close each file in specific order. I have the latest version of autoit and tried to start with opening excel itself and mathcad file using following.: $Var = MsgBox(33,"Confirmation","Run script?") if $Var = 6 Then Run("C:\Program Files(x86)\Microsoft Office\Office14\excel.exe", "", @SW_SHOWMAXIMIZED) ShellExecute("Input_names.xmcd","","S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs") EndIf But nothing happens. I have some time issues otherwise I would study it thoroughly. Any help is greatly appreciated. Edited July 15, 2014 by Ytaj Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 Welcome to AutoIt and the forum! Did you have a look at the Excel UDF that comes with AutoIt? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Ytaj Posted July 15, 2014 Author Share Posted July 15, 2014 (edited) Thanks! Welcome to AutoIt and the forum! Did you have a look at the Excel UDF that comes with AutoIt? You mean the _Excel*** commands? I have somehow managed to open the excel file using the code (without understanding much of it) in help file of the autoit: #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object Local $oAppl = _Excel_Open() If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; ***************************************************************************** ; Open an existing workbook and return its object identifier. ; ***************************************************************************** Local $sWorkbook = @ScriptDir & "\Input_data.xls" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) But when putting it under the "if" command - so it only runs when OK is clicked, nothing happens and no error is displayed. #include <Excel.au3> #include <MsgBoxConstants.au3> if $Var = 6 Then ; Create application object Local $oAppl = _Excel_Open() If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; ***************************************************************************** ; Open an existing workbook and return its object identifier. ; ***************************************************************************** Local $sWorkbook = @ScriptDir & "\Input_data.xls" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)EndIf Also how about the mathcad files and inclusion of delays before closing it? Edited July 15, 2014 by Ytaj Link to comment Share on other sites More sharing options...
Jfish Posted July 15, 2014 Share Posted July 15, 2014 (edited) The problem is that $var will not equal 6. You have an "OK" button - not a "Yes" button. OK will return 1 and cancel 2. If you add this line after you create the message box it will show you the values in the console if you are using Scite: ConsoleWrite(@crlf&"this is the value of $var "&$var&@crlf) The @crlf are carriage return macros so the information appears on its own line. You could also change the box to yes no this way (and keep the 6): $Var = MsgBox(4,"Confirmation","Run script?") Edited July 15, 2014 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
Ytaj Posted July 15, 2014 Author Share Posted July 15, 2014 Ok thanks. I am starting to understand this. I have almost managed to do what I want. But two things remain unsolved: - Excel save is not working, I am not sure if there is some error in my code - it does not say anything and executes things like charm, but files are not updated - Is it possible to save and exit mathcad file? Here is my code: expandcollapse popup#Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile=test.Exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.12.0 Author: YAT Script Function: Template AutoIt script. #ce ---------------------------------------------------------------------------- #include <Excel.au3> #include <MsgBoxConstants.au3> $Var = MsgBox(4,"Bekræftelse","Kør script?") if $Var = 6 Then Local $oAppl = _Excel_Open() ; Create application object If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; ***************************************************************************** ; Open an existing workbook and return its object identifier. ; ***************************************************************************** Local $sWorkbook1 = @ScriptDir & "\Input_data.xls" Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Sleep(1000) _Excel_BookSave ( $oWorkbook1 ) _Excel_BookClose ( $oWorkbook1 , True ) Sleep(1000) _Excel_Close ( $oAppl ) ShellExecuteWait ( "Input_names.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open" ) ShellExecuteWait ( "Appendix B.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open" ) Local $sWorkbook2 = @ScriptDir & "\Steel sub grade.xls" Local $oApp2 = _Excel_Open() Local $oWorkbook2 = _Excel_BookOpen($oApp2, $sWorkbook2, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook2 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Sleep(1000) _Excel_BookSave ( $oWorkbook2 ) _Excel_BookClose ( $oWorkbook2 , True ) _Excel_Close ( $oApp2 ) ShellExecuteWait ( "Appendix B.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open" ) Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 How do you notice that _Excel_BookSave is not working? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 (edited) In the code you posted the Workbok doesn't get changed - so Excel doesn't write the file to disk but sets the return value to 1 and @extended to 0. You open and close Excel after each _Excel_BookSave. That's not needed. Open Excel once and close it at the end of your script: #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile=test.Exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iValue $Var = MsgBox(4,"Bekræftelse","Kør script?") ; Create application object if $Var = 6 Then Local $oAppl = _Excel_Open() If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; Open an existing workbook and return its object identifier. Local $sWorkbook1 = @ScriptDir & "\Input_data.xls" Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $iValue = _Excel_BookSave($oWorkbook1) MsgBox(0, "Book1", "Return Value = " & $iValue & ", @extended = " & @extended) _Excel_BookClose($oWorkbook1, True) ShellExecuteWait("Input_names.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open") ShellExecuteWait("Appendix B.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open") Local $sWorkbook2 = @ScriptDir & "\Steel sub grade.xls" Local $oWorkbook2 = _Excel_BookOpen($oApp, $sWorkbook2, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook2 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $iValue = _Excel_BookSave($oWorkbook2) MsgBox(0, "Book2", "Return Value = " & $iValue & ", @extended = " & @extended) _Excel_BookClose($oWorkbook2, True) ShellExecuteWait("Appendix B.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open" ) _Excel_Close($oApp) Edited July 15, 2014 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Ytaj Posted July 15, 2014 Author Share Posted July 15, 2014 (edited) It is beacuse I can see it in the explorer - change date and time is same before and after running the script. Obviously this is just a test file, in the final version the code or excel file MUST be saved with new data. Also, the idea is to open excel so it reads data and refreshes cells - after which I open mathcad and do the calculations with the new data from excel file. I am not sure if mathcad can read refreshed data if exel is open. That is why I try ti save the document and close it before opening mathcad file. *oh! .. I understood, so the application does not need to be closed - just the worksheet/workbook which is taken care of by the _Excel_BookClose And mathcad? .. is it possible to save and exit or do i have to do it manually? Edited July 15, 2014 by Ytaj Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 I've added a MsgBox after each _Excel_BookSave in my above script code. You should get Return Value = 1, @extended = 0 For both Workbooks because they haven't been changed by your script. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 I noticed another thing. Parameter 5 of _Excel_BookOpen specifies the password needed to open the workbook. You've set this parameter to True. How do you enter the password? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Ytaj Posted July 15, 2014 Author Share Posted July 15, 2014 (edited) I noticed another thing. Parameter 5 of _Excel_BookOpen specifies the password needed to open the workbook. You've set this parameter to True. How do you enter the password? No there is no password - but it can open excel files anyway. I will change it to False. and I do get the message box that you mentioned above. Also I was trying to further "automatize" some functions inside mathcad using Send ( "^{F9}" , 0 ) (that is CTR+F9), but it does not do anything. Edited July 15, 2014 by Ytaj Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 If there is no password needed then you can strip down the command to Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted July 15, 2014 Share Posted July 15, 2014 Also I was trying to further "automatize" some functions inside mathcad using Send ( "^{F9}" , 0 ) (that is CTR+F9), but it does not do anything. Is the mathcad window active? Because Send "Sends simulated keystrokes to the active window." My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Ytaj Posted July 16, 2014 Author Share Posted July 16, 2014 Is the mathcad window active? Because Send "Sends simulated keystrokes to the active window." Well yes, when the application opens it is "pre-activated" and i can use normal key commands without "activating it" Link to comment Share on other sites More sharing options...
water Posted July 16, 2014 Share Posted July 16, 2014 MathCAD seems to provide a COMOLE interface for Visual basic etc. It's a bit more complex but much more reliable compare to automating the GUI. But I'm not sure you want to go this route as you seem to be new to AutoIt and coding. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Ytaj Posted July 16, 2014 Author Share Posted July 16, 2014 (edited) Yes, it is better not to move into that direction. I thought this approach would work, where I ensure that window is activated - then just close and press enter upon save request: ShellExecuteWait("Input_names.xmcd" , "" , "S:\Projekter\Siemens Wind Power AS\2014\2014108 - Tower transport\Dokumentation (skabeloner)\COPY THIS FOLDER\Lifting\Rev00\01_Calculation\01_Analytical\Appendix B - Calcs\" , "open") ; Wait 10 seconds for the mathcad window to appear. Local $hWnd = WinWait("Mathcad - [Input_names.xmcd]", "", 3) ; Keep the mathcad window active when using the Send function. SendKeepActive("Mathcad - [Input_names.xmcd]") Send( "^{F9}") ; Disable the mathcad window being active when using the Send function. SendKeepActive("") ; Close the mathcad window using the handle returned by WinWait. WinClose($hWnd) Sleep(500) Send("{ENTER}") But as before, no error messages and no action. ... I guess I just have to save and close manually. Edited July 16, 2014 by Ytaj Link to comment Share on other sites More sharing options...
water Posted July 16, 2014 Share Posted July 16, 2014 Remove the wait parameter from WinWait or check the return value Local $hWnd = WinWait("Mathcad - [Input_names.xmcd]") ; Use this line If $hWnd = 0 Then ... ; or this line When removing the timeout parameter your script will wait forever if the specified window never appears or has a different title. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - 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