CompTech810
asked on
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?
Thanks!
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
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER