By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,929 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,929 IT Pros & Developers. It's quick & easy.

Running Excel macro from Access module...

P: 4
Hello,
This can't be that hard! Could someone give me the code to run a macro in my Personal.xls from my Access module? I have created a query in Access, exported it to a network drive, I can open it, but now need to format it.

This is what I have, it works fine to open the Personal.xls but not run the Format_TerrListing macro.

(Previous code here......)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Dim sMacros As String, tMacros As String
'Dim CurrentProject As String

' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")

' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0

' Full path of excel file to open

On Error GoTo ErrHandle

sPath = "P:\TerritoryListings\"
sFullPath = sPath & mFilename
sMacros = "V:\Documents and Settings\CBalser\Application Data\MicroSoft\Excel\XLSTART\PERSONAL.XLS"
DoCmd.RunMacro "Format_TerrListing" ' this doesn't work

' Open it
With oXL
.Visible = True
.workbooks.Open (sFullPath)
.workbooks.Open (sMacros)
End With


Thanks for setting me straight with the correct syntax and code!!
CBalser
Sep 25 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You can use the Run method of the Excel Application object to run your macro. However, the workbook in which the macro is contained must be open for the macro to be available to the automation server, so you will have to open it first, as shown in the sample code below:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Excel.Application
  2. Set objExcel = New Excel.Application
  3. objExcel.Workbooks.Open(sMacros)
  4. objExcel.Visible = True ' use for testing to make sure all is working - no need for application to be visible if it is to be closed automatically
  5. objExcel.Run ("personal.xls!Format_TerrListing")
  6. objExcel.Quit ' assuming you don't need to save this workbook
  7. Set objExcel = Nothing
-Stewart
Sep 25 '08 #2

P: 4
Hi. You can use the Run method of the Excel Application object to run your macro. However, the workbook in which the macro is contained must be open for the macro to be available to the automation server, so you will have to open it first, as shown in the sample code below:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as Excel.Application
  2. Set objExcel = New Excel.Application
  3. objExcel.Workbooks.Open(sMacros)
  4. objExcel.Visible = True ' use for testing to make sure all is working - no need for application to be visible if it is to be closed automatically
  5. objExcel.Run ("personal.xls!Format_TerrListing")
  6. objExcel.Quit ' assuming you don't need to save this workbook
  7. Set objExcel = Nothing
-Stewart


Thanks a bunch, Stewart!! I'm dealing with other problems, but at least THAT works now! Best regards! cb
Sep 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.