control Excel macro from within Access office 2002

[Guest]

I have a macro in Access that opens an Excel Spreadsheet. I then run a macro
from within Excel, after execution close Excel and return to Access. Can I
possibly run the Excel macro from within Access?
I have to use Excel to get the data from an external source, refresh from
within Excel, save and then use the data via link to the spreadsheet from
within access.
All works fine, but running the Excel macro from within Access would save
steps for the user.
Thanks for any help.
Brigitte P.

 

[Ken Snell [MVP]]

Some generic code to run an EXCEL macro from ACCESS:

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub

 

[Ken Snell [MVP]]

Sorry... neglected to note that I was in macros group when I posted the
sample VBA code for running an EXCEL macro. You cannot do this directly with
an ACCESS macro action. But it can be done via VBA code (as I posted). You
can run VBA code from a macro by using the RunCode action to run a public
function, which would then do the macro. However, this function would not be
generic, as you'd need to write the code for a specific EXCEL file and macro
names.

Post back with more info and we'll see what we can do to help.