Running excel macro from an Access Macro

  • Thread starter Thread starter Matt
  • Start date Start date
M

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?
 
Matt said:
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?
Click to expand...

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"
 
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"
 
This worked perfect. Thanks!

akphidelt said:
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"
Click to expand...
 
Back
Top