Access running a Excel personal macro

Hello Experts,

Hoping someone can help me.

I have Access 2007 and using a VBA script to:

1.  Export sales data to an Excel sheet to drive c:\SalesUpdateNew.xlsx
2.  Open PERSONAL.XLSB, which contains the macros I need to run in SalesUpdateNew.xlsx

THe VBA is opening the PERSONAL.XLSB and SalesUpdateNew.xlsx but failing to run the PERSONAL.XLSB macro in SalesUpdateNew.xlsx.

When run using the VBA the macros don't show in "All open Workbooks" in the SalesUpdateNew.xlsx workbook". They do show in PERSONAL.XLSB.   When I manually open SalesUpdateNew.xlsx, it works. What am I missing in my code?

Private Sub Command0_Click()
Dim x2 As Object
Dim x3 As Object


DoCmd.RunMacro "Export Sales Update2"   ' Export sales data excel workbook (SalesUpdateNew.xlsx) to c:\

       
    Call WaitFor(5) ' Wait 5 seconds
       Set x2 = CreateObject("Excel.Application")
       x2.Workbooks.Open ("C:\Documents and Settings\USER\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
       x2.Visible = True
        
    Call WaitFor(5) ' Wait 5 seconds
      Set x3 = CreateObject("Excel.Application")
       x3.Workbooks.Open ("C:\SalesUpdateNew.xlsx")
       x3.Visible = True
       x3.Application.Run "Personal.xlsb:SalesUpdate"

End Sub

Open in new window


Thanks!
LVL 2
CompTech810Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
You're running different instances of Excel so personal.xlsb is not available in the second one. Try this:
Private Sub Command0_Click()
Dim x2 As Object
Dim x3 As Object


DoCmd.RunMacro "Export Sales Update2"   ' Export sales data excel workbook (SalesUpdateNew.xlsx) to c:\

       
    Call WaitFor(5) ' Wait 5 seconds
       Set x2 = CreateObject("Excel.Application")
       x2.Workbooks.Open ("C:\Documents and Settings\USER\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
       x2.Visible = True
        
    Call WaitFor(5) ' Wait 5 seconds
       x2.Workbooks.Open "C:\SalesUpdateNew.xlsx"
       x2.Run "Personal.xlsb!SalesUpdate"

End Sub

Open in new window

0
 
CompTech810Author Commented:
Thanks!!  Worked!!
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.

All Courses

From novice to tech pro — start learning today.