Importing VBA module fails

[NassauSky]

Just needed to import a module and the code from  @Subz is failing at $oExcel.VBE.ActiveVBProject.VBComponents.Import($sModule)

#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.xls", Default, True)
$oExcel.Application.Run("MergeWkbks")

 

Any ideas to get the Import to work?

 

Thanks.

 

 

 

[water]

Which error message do you get?
What is the value of @error and @extended?
Did you try to implement a COM error handler for more detailed information?

[NassauSky]

Thanks @water.

For anyone else interested in solving the problem:

I found another example which instead writes a worksheet module rather than global module and it had the COM error handler which I used and realized that my updated 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. The solution is here which includes the com debug function (and don't forget to enable trust access to the VBA project object model):

 

#include <Excel.au3>
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Local $sModule = @TempDir & "\Module.bas"
Local $sMacro
    $sMacro &= 'Attribute VB_Name = "Module1"' & @CRLF
    $sMacro &= 'Sub btn1()' & @CRLF
    $sMacro &= 'MsgBox "Test Succeeded"' & @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.xls", Default, True)
$oExcel.Application.Run("Module1.btn1")


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

 

Edited January 20, 2020 by NassauSky