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:
#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.
