• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3441
  • Last Modified:

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"
0
schwientekd
Asked:
schwientekd
  • 3
  • 2
1 Solution
 
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

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

Set oBook = oXL.Workbooks.Open(oxL.startuppath & "\Personal.xlsb")
0
 
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

0
 
Ken ButtersCommented:
whoops... looks like you already found it while I was typing my short manuscript! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now