How to import a macro to Excel by VBS

user_2435924 0
Viewed 622

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.

import vbscript excel
1 Answers

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).

user_24359240
Related