Open an excel spreadsheet and run a macro from Access VB

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I have an Access 2007 database that creates an Excel spreadsheet via the
DoCmd.OutputTo method. The method creates and opens the Excel spreadsheet.
Is there any way to have Access run an Excel macro from another spreadsheet
in the one that was just created?

(I need Access to create DataSpreadsheet, open DataSpreadsheet, then run a
macro from MacroSpreadsheet in DataSpreadsheet)
 
I'm not sure if you run Excel code in Access, per se, but why don't you just
put your excel code inside of an access module???


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 Rep()
Dim strFile As String
strFile = "C:\MyExcelWorkbook.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…you can record a macro and make the process
super easy!!
End With

' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing

End Sub
 
'********************************
'* 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