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.

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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Imran Ghani" wrote:

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.

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.

"ryguy7272" wrote:

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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Imran Ghani" wrote:

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.