Jump to content
NassauSky

Importing VBA module fails

Recommended Posts

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.

 

 

 

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...