Sign in to follow this  
Followers 0
Ytaj

Excel and Mathcad files

17 posts in this topic

#1 ·  Posted (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 by Ytaj

Share this post


Link to post
Share on other sites



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

#3 ·  Posted (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 by Ytaj

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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:

 

#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" )

Share this post


Link to post
Share on other sites

How do you notice that _Excel_BookSave is not working?


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

#7 ·  Posted (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 by water

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

#8 ·  Posted (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 by Ytaj

Share this post


Link to post
Share on other sites

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

#11 ·  Posted (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 by Ytaj

Share this post


Link to post
Share on other sites

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

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

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"

Share this post


Link to post
Share on other sites

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

#16 ·  Posted (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 by Ytaj

Share this post


Link to post
Share on other sites

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 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
Sign in to follow this  
Followers 0