Run MS Excel Macro From MS Access

[Imran Ghani]

Hi!
I open an excel sheet from MS Access. I have made a macro in Excel sheet. I
want to run the Macro Programmatically from with MS Access Code. Kindly guide
me how to run the Excel Macro from with MS Access.
Regards,
Imran.

 

[ryguy7272]

You've come to the right place! It is quite easy to perform operations in
Excel, and control the entire process from Access. Make sure you set a
reference to Excel, and then run this code in an Access module:

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub ControlExcelFromAccess()
Dim strFile As String

strFile = "C:\Documents and Settings\Ryan\Crosstab Query.xls"

‘Of course, this is just an example; put the actual path to your actual file
here...
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here...

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing

End Sub

HTH,
Ryan---

 

[Imran Ghani]

Hi Ryan,
Thanks for your detailed guide.
I have pasted the code in my module, and tried to run it, but it is giving a
complie error of "user-defined type not defined" , and it's pointing to the
following piece of code.
Private objExcel As Excel.Application
Kindly guide me what I am missing to successfully run the code.
Regards,
Imran.