Creating Excel Macros From Access

[Arkansas Lady]

How to you create an Excel Macro using VBA in Access? Thank you for your
assistance.

 

[OssieMac]

See if the following example will help you get started.

Public Sub DemoExcelFromAccess()

Dim strCurrProjPath As String 'Path of this Access database
Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Excel Worksheet
Dim strXlPathFileName As String 'Path and Excel filename

'Path of current Access project
strCurrProjPath = Application.CurrentProject.Path

'Assign Path and filename of XL file to variable
'Note:In this example Test Workbook.xls in same folder
strXlPathFileName = strCurrProjPath & "\" & "Test Workbook.xls"

'Assign Excel application to a variable
Set objExcel = CreateObject("Excel.Application")
'objExcel.Visible = True 'Can be visible or not visible
objExcel.Visible = False

'Open the Excel Workbook
Set objWorkbook = objExcel.Workbooks.Open(strXlPathFileName)

'Loop through worksheets in workbook
For Each objWorksheet In objWorkbook.Worksheets
MsgBox objWorksheet.Name
Next objWorksheet

Set objWorksheet = Nothing

'Close and Save the Excel workbook
objWorkbook.Close SaveChanges:=False

Set objWorkbook = Nothing

objExcel.Quit

Set objExcel = Nothing

End Sub

 

[OssieMac]

Forgot to tell you that while in the Access VBA editor, Select Tools ->
References and check the box against Microsoft Excel 10.0 Object library.
(9.0 for 2000, 10.0 for 2002, 11.0 for 2003 and 12.0 for 2007 depending on
which version you are using.) Ensure that you actually check the box. Don't
do what I did the first time and just selected the option and then wondered
why it did not work.