Solved

Access running a Excel personal macro

Posted on 2013-06-18
2
1,531 Views
Last Modified: 2013-06-18
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!
0
Comment
Question by:CompTech810
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 200 total points
ID: 39256629
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
 
LVL 2

Author Closing Comment

by:CompTech810
ID: 39256662
Thanks!!  Worked!!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question