Call a macro in a spreadsheet that is already open

  • Thread starter Thread starter kfguardian
  • Start date Start date
K

kfguardian

Can a macro in a spreadsheet be invoked in Access code? If the file is
already open, I just find it and export to it. Will I have to close the file
and reopen it to run the auto macro??
 
Here is a VBA subroutine that can be used to run an EXCEL macro in an EXCEL
file that is already open.

'********************************
'* 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
 
Back
Top