Running Excel Macros from AutoIt Work Individually, But Not Sequentially

[onderijw]

I have created an AutoIt script that executes macros I have created that copies charts and table data from excel sheets into a powerpoint presentation that summarizes the information.

Here is the code I run:
 

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>

AutoItSetOption('TrayIconDebug', 1)
AutoItSetOption('MouseCoordMode', 0) ;Ensures mouse clicks are relative to window, not the overall screen

;put this anywhere in the code, down the bottom is fine :)
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Error Test","We intercepted a COM Error !"   & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description   & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "   & @TAB & $HexNumber        & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "   & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile   & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    ClipPut ("We intercepted a COM Error !"  & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description   & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "   & @TAB & $HexNumber        & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "   & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile   & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
             )
  SetError(1); to check for after this function returns
Endfunc


Local $oExcelApp = _Excel_Open()
Local $sTemplateFilePath = "C:\Users\myname\Documents\Updated Report Files\TemplateSheet.xlsm"
Local $oTemplateWorkbook = _Excel_BookOpen($oExcelApp, $sTemplateFilePath)
Local $reportType = "areaOfFocus"

WinActivate($oTemplateWorkbook)
Local $reportDate = "08 12 2019"

$oExcelApp.run($reportType & "_1_Macro." & $reportType & "_1_Macro", $reportDate)
$oExcelApp.run($reportType & "_2_Macro." & $reportType & "_2_Macro", $reportDate)
$oExcelApp.run($reportType & "_3_Macro." & $reportType & "_3_Macro", $reportDate)
$oExcelApp.run($reportType & "_4_Macro." & $reportType & "_4_Macro", $reportDate)
$oExcelApp.run($reportType & "_5_Macro." & $reportType & "_5_Macro", $reportDate)
$oExcelApp.run($reportType & "_5_Shift_Macro." & $reportType & "_5_Shift_Macro", $reportDate)
$oExcelApp.run($reportType & "_6_Macro." & $reportType & "_6_Macro", $reportDate)
$oExcelApp.run($reportType & "_7_Macro." & $reportType & "_7_Macro", $reportDate)

 

These macros require a date and the "area of Focus" to know which directory to open up and copy and paste the excel sheet information into the ppt. What is really weird is that when I run this code, the first macro opens fine, but then get this error pertaining to the second macro line.

In this situation PreTrim is the area of focus, and the 104 macro would be the second macro line executed. So the first macro executes perfectly, and then I get the COM error on this line:

$oExcelApp.run($reportType & "_2_Macro." & $reportType & "_2_Macro", $reportDate)
 

If I comment out the first macro line, and RE-RUN the script, then the rest of the macros run fine. If I run each macro individually, they all run fine as well.

I even have a copy of all of these 8 macros, that run for a different area of focus, that are run in the SAME way (8 macros, executed one after the other), and it works fine. All of these macros are based out of 1 general template file I use to copy and past Excel data. So the idea that macros are not enabled doesn't seem to be the actual problem here...

I know I have not incuded any VBA code here, but it seems like it shouldn't really affect your help if I have confirmed all the macros work individually. It's simply how they run together in AutoIt and perhaps how I am calling the macros.

If it helps, when I run the macros, the first macro opens a template, copies and pastes some tables and charts, and then saves the powerpoint as a new powerpoint on my desktop. Then, I close the powerpoint (I know I can leave it open, and continually copy and paste data, I just choose to currently run it opening and closing ppt for every macro - I also checked this doesn't solve my problem if I DO keep ppt open). I then re-open the powerpoint in the next macro, and copy and paste different data. I do this for each macro. I also checked to see that the first macro opened and saved the SAME powerpoint file the other macros used and that didnt change anything. So I don't think the problem is related to how the powerpoint is being saved?

I can try to provide some edited code if needed however, and any help is much appreciated. Been a long time lurker this Summer, and appreciate the community here.

Edited August 23, 2019 by onderijw

[Nine]

I highly doubt someone could answer to your issue.  My first reflex would be to convert everything in autoit using excel and powerpoint UDFs.  And use COM whenever required.  I believe it would make your life much  easier to use only one single technology...

[seadoggie01]

I had a similar issue, so I added some delay (I think 2 seconds?), but found moved to full AutoIt later. It seems like Excel isn't always immediately ready after it's done executing a macro. But as Nine says, this really shouldn't be the way you execute the code. If you can't get everything into AutoIt, at least stop running the 8 macros in a row from AutoIt, just make a macro that calls them and execute the macro once.

[onderijw]

Thank you both Nine and seadoggie01, I am definitely on the amateur side of scripting/VBA so my hope was that my code or reasoning in general, apart from my actual problem, would get some much needed constructive criticism (which it has!  )

At the beginning I used AutoIt to generate my excel reports from an online database, and then concurrently developed the macros to format them from excel into powerpoint and it just naturally made sense to me to think I could bridge them, leading to my problem. I will definitely look into the Excel and PowerPoint UDF's though.

"In the end" (still very much a work in progress), I took the idea to execute the macros from 1 single macro and that has rid me of the COM errors. It makes sense that concentrating most of the code in 1 language would reduce any problems between AutoIt and VBA. Would it be right to assume that using COM can be finicky? I have little experience working between programs like that but I think it would be interesting to know as I continue to learn and try new things in AutoIt!

[Nine]

On 8/28/2019 at 7:58 AM, onderijw said:

Would it be right to assume that using COM can be finicky?

I agree that using solely COM would make it much fastidious for you since the UDF are essentially wrappers over COM statements.  You would be reinventing the UDF.  But from time to time, COM can be very handy when the UDF cannot provide the required functionality.

[water]

What I understand so far:
You run macro "PreTrim_1_Macro.PreTrim_1_Macro" but the error message talks about ""PreTrim_104_Macro.PreTrim_104_Macro".
Where does this new macro reside? The error message imho clearly describes what the problem is.