asked on

Vbscript import excel macro to personal.xls

I'm using Excel 2007 and I have some macros that I have exported to .bas files. I would like to import these .bas files into the personal.xls file so that they may be used whenever excel is opened. So far I have attempted to do this using a vbscript. I've got the following script which will open up a new excel workbook and import the .bas files. I just need to figure out how to get them imported into the personal.xls file and not a new excel doc, which doesn't save the macros when excel is opened.

Dim oXL, oBook, oSheet

' Create a new instance of Excel and make it visible.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Add a new workbook and set a reference to Sheet1.
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Sheets(1)

'Import previously created BAS module file
oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module1.bas"
oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module2.bas"

ASKER CERTIFIED SOLUTION

Ken Butters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwientekd

ASKER

I get an error

Line: 8
Char: 6
Error: Subscript out of range
Code: 800A0009
Source: Microsoft VBScript runtime error

Looks like it can't find personal.xlsb with this line of code.
Set oBook = oXL.Workbooks(oxL.startuppath & "\Personal.xlsb")

schwientekd

ASKER

Here is what worked. The line needed to be oXL.Workbooks.Open

Set oBook = oXL.Workbooks.Open(oxL.startuppath & "\Personal.xlsb")

Ken Butters

Here is correction.

oXl.Workbooks.open oxL.startuppath & "\Personal.xlsb"

the workbooks.open command takes as a parameter, the full path including the name, of your workbook, so first step is to open the book correctly (which I did not do on the first go). the "StartupPath" method is part of the workbooks collection and resolves to the path where you Personal.xlsb is stored.

Set oBook = oXL.Workbooks("Personal.xlsb")

After the workbook is open, it is now a part of the workbooks collection and can be referenced by just the name, not the path.

At this point the rest of your script will work the same as any other workbook.

NOTE: This script does assume that your Personal.xlsb already exists. If not we could adjust the code to create one, just let me know if that is necessary.

Dim oXL, oBook, oSheet

    ' Create a new instance of Excel and make it visible.
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True

    ' Add a new workbook and set a reference to Sheet1.
     oXl.Workbooks.open oxL.startuppath & "\Personal.xlsb"
     Set oBook = oXL.Workbooks("Personal.xlsb")
     
     Set oSheet = oBook.Sheets(1)

    'Import previously created BAS module file
     oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module1.bas"
     oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module2.bas"

Open in new window

Ken Butters

whoops... looks like you already found it while I was typing my short manuscript! :)