Running excel macro from an Access Macro

[Matt]

I have an Access 2000 macro that opens an Excel workbook. After the open
workbook command, I would like an excel macro to automatically run. Is there
a way to program this in the same Access macro?

 

[Stuart McCall]

I have an Access 2000 macro that opens an Excel workbook. After the open
workbook command, I would like an excel macro to automatically run. Is
there
a way to program this in the same Access macro?

Make the name of your macro 'AutoExec' (no quotes). That will run when the
workbook is opened.

In Access, open the workbook using:

Application FollowHyperlink "c:\MyPath\MyWorkbook.xls"

 

[akphidelt]

Here is an example of how I do it

Dim objXls As Excel.Application
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet
Dim myFile As String
Dim SheetsCount As Integer

On Error GoTo Excel_Open
DoCmd.TransferSpreadsheet acExport, 8, "yourTBLorQRY", "YourWorkbookPath",
True

Set objXls = CreateObject("Excel.Application")
myFile = "YourWorkbookPath.xls"
objXls.Workbooks.Open ("" & myFile)
objXls.Visible = True
Set MyBook = objXls.Workbooks("YourWorkBookName.xls")

SheetsCount = MyBook.Worksheets.Count

Set MySheet = MyBook.Worksheets(SheetsCount)
MySheet.Activate
MySheet.Application.Run "Macro Name"

 

[Matt]

This worked perfect. Thanks!

Here is an example of how I do it

Dim objXls As Excel.Application
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet
Dim myFile As String
Dim SheetsCount As Integer

On Error GoTo Excel_Open
DoCmd.TransferSpreadsheet acExport, 8, "yourTBLorQRY", "YourWorkbookPath",
True

Set objXls = CreateObject("Excel.Application")
myFile = "YourWorkbookPath.xls"
objXls.Workbooks.Open ("" & myFile)
objXls.Visible = True
Set MyBook = objXls.Workbooks("YourWorkBookName.xls")

SheetsCount = MyBook.Worksheets.Count

Set MySheet = MyBook.Worksheets(SheetsCount)
MySheet.Activate
MySheet.Application.Run "Macro Name"