How to import a macro to Excel by VBS

Question

I need to create a new Excel file (from a .csv) and import two macro files: One .bas and one .cls. The first import works, but the second one is not imported to ThisWorkbook, but to a new class module.

At first I create the new excel file object from my existing csv file.

Dim oExcel
    Set oExcel = CreateObject("Excel.Application")
    Dim oBook
    Set oBook = oExcel.Workbooks.Open(srcFile)

Then the .bas is imported to the modules directory by:

    oBook.VBProject.VBComponents.Import scriptPath & "\" & "MyMacro" & ".bas"

When I do the same call for the .cls file it is imported to a new class module directory and not to ThisWorkbook.

    oBook.VBProject.VBComponents.Import scriptPath & "\" & "RightClickEntry" & ".cls"

It must be imported to ThisWorkbook because it adds an entry to the right-click context menu which will else not be shown.

First question why don't you just create a template file with all the code included and import the CSV data into the template (that would be a much better an easier approach). Second you cannot "import" any code to ThisWorkbook because .cls files are always imported as class modules. See the code in ThisWorkbook and the workbook itself as one union. While you export the code from ThisWorkbook it looses the connection to the workbook itself and cannot be linked again by importing because the new workbook already has a ThisWorkbook code (even if it is empty). — Pᴇʜ
Thank you. Yes I think that would be better: Open the template which has everything in it, import the csv data and save it as new excel file. — Daniel

Daniel Daniel · Accepted Answer · 2019-06-24T07:29:44

What I was looking for seems to be impossible because of the union like nature ot ThisWorkbook. I will create a template Excel file with all macros, open this in my script, insert all csv data and save it as new Excel file. This approach is also better because it requires only one additional file (the template) beneath the script and not two exported macro files (or more in the future).

How to import a macro to Excel by VBS

1 Answers