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"
schwientekdIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
try this:

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(oxL.startuppath & "\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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwientekdIT DirectorAuthor Commented:
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")
schwientekdIT DirectorAuthor Commented:
Here is what worked.  The line needed to be oXL.Workbooks.Open

Set oBook = oXL.Workbooks.Open(oxL.startuppath & "\Personal.xlsb")
Ken ButtersCommented:
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 ButtersCommented:
whoops... looks like you already found it while I was typing my short manuscript! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.