Invoking vba script via autoit Run not working

[Becca]

I am trying to create an autoit script that will create and save an Excel workbook, import a vba script/macro and then open the workbook and run the script. I have it working up to the Run at which point I get an error. My autoit script and the vba code are below.  Googling and posted samples have helped me get to where I am now but I am stuck getting the macro to run. The error I get (on the last line) is "The requested action with this object has failed." Any help would be greatly appreciated. Thank you.

autoit script:

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

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Create a new workbook and save it
; Create the new workbook
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Save the workbook (replacing existing file)
_Excel_BookSaveAs($oWorkbook, @DesktopDir & "\MyNewExcel2.xls", Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oExcel.VBE.ActiveVBProject.VBComponents.Import(@DesktopDir & "\Module1.bas")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error importing." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookOpen($oExcel,@DesktopDir & "\MyNewExcel2.xls")
;$oExcel.Run("Module1.bas")
$oExcel.Application.Run("MyNewExcel2.xls!Module1.bas")

macro code:

Attribute VB_Name = "Module1"
Sub MergeWkbks()
Path = "C:\Users\rebecca.bryant\Desktop\MergingExcelTest\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

 

[Juvigy]

Try it like this:   $oExcel.Application.Run("Macro")  Where 'Macro' is the name of your macro. Also i see that your macro code is wrong or incomplete. Does it run when you manually start it?

[Becca]

Thank you for your reply Juvigy. I have tried  $oExcel.Application.Run("Module1.bas") and that doesn't work either. And yes, the macro does run if I manually start it. What are the errors that you see in the macro?

Thanks again

[Becca]

I have made some changes to the macro as per an amended version that I found on stackoverflow:

Option Explicit
Const path As String = "C:\MergingExcelTest\"
Sub GetSheets()
Dim FileName As String
Dim wb As Workbook
Dim sheet As Worksheet

FileName = Dir(path & "*.xls*")
  Do While FileName <> ""
  Set wb = Workbooks.Open(FileName:=path & FileName, ReadOnly:=True)
     For Each sheet In wb.Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next sheet
     wb.Close
     FileName = Dir()
  Loop
End Sub

[Subz]

This worked fine for me, in Office 2013 SP1 32 bit.

#include <Excel.au3>

Local $sModule = @TempDir & "\Module.bas"
Local $sMacro
    $sMacro &= 'Attribute VB_Name = "Module1"' & @CRLF
    $sMacro &= 'Sub MergeWkbks()' & @CRLF
    $sMacro &= 'Path = "' & @DesktopDir & '\MergingExcelTest\"' & @CRLF
    $sMacro &= 'Filename = Dir(Path & "*.xlsx")' & @CRLF
    $sMacro &= '    Do While Filename <> ""' & @CRLF
    $sMacro &= '    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True' & @CRLF
    $sMacro &= '            For Each Sheet In ActiveWorkbook.Sheets' & @CRLF
    $sMacro &= '            Sheet.Copy After:=ThisWorkbook.Sheets(1)' & @CRLF
    $sMacro &= '    Next Sheet' & @CRLF
    $sMacro &= 'Workbooks(Filename).Close' & @CRLF
    $sMacro &= 'Filename = Dir()' & @CRLF
    $sMacro &= 'Loop' & @CRLF
    $sMacro &= 'End Sub' & @CRLF
    Local $hFileOpen = FileOpen($sModule, 2)
    FileWrite($hFileOpen, $sMacro)
    FileClose($hFileOpen)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
$oExcel.VBE.ActiveVBProject.VBComponents.Import($sModule)
_Excel_BookSaveAs($oWorkbook, @DesktopDir & "\MyNewExcel2.xlsm", Default, True)
$oExcel.Application.Run("MergeWkbks")

@Becca can you place your code between <> just makes it easier to read.

Edited October 28, 2021 by Subz

[Juvigy]

12 hours ago, Becca said:

Thank you for your reply Juvigy. I have tried  $oExcel.Application.Run("Module1.bas") and that doesn't work either. And yes, the macro does run if I manually start it. What are the errors that you see in the macro?

Thanks again

Your error is that you try to run the file name. You need to run the macro/function name.  I would guess you need to use " GetSheets "

[Becca]

Thank you Subz. That worked!!! And thank you Juvigy also. (And in the future I will use <> for posting code.)

[NassauSky]

Hi all,

Anyone trying this on a newer version of Excel might run into 2 errors. 

Excel 2016 required me to enable "Trust access to the VBA project object model" in Trust Center Settings.

I also noticed something else.  The last line $oExcel.Application.Run("MergeWkbks") also failed with an error.  I had to point it to the module which hosted the code.