Create an Excel File From Within Access, Modify and Save It
It can be useful to create an Excel workbook from within Microsoft Access, modify and format the contents of the Excel workbook, and save it to the directory of your choice.
The code below illustrates how to accomplish that task. Keep in mind that the user must include the Microsoft Excel Object Library in the Tools, Reference section of the Visual Basic Editor within Microsoft Access.
Program Code
Option Compare Database
Option Explicit
Public Sub CreateExcelFileFromAccess()
' ***************************************************************************
' From within Access, Create An Excel Workbook, Update the Workbook
' Contents, and then Save the Workbook
' ***************************************************************************
' ***************************************************************************
' In the VBE (Visual Basic Editor of Access), in Tools, References
' Include the Microsoft Excel Object Library
' ***************************************************************************
Dim XL As Excel.Application
Dim WB As Excel.Workbook
Dim WKS As Excel.Worksheet
' ***************************************************************************
' Create an Excel Instance With No Workbooks Yet
' ***************************************************************************
Set XL = New Excel.Application
' ***************************************************************************
' Optional: Make it Visible
' ***************************************************************************
XL.Visible = True
' ***************************************************************************
' Add a Workbook and Worksheets To The Excel Instance
' ***************************************************************************
Set WB = XL.Workbooks.Add
' ***************************************************************************
' Rename the first Worksheet
' ***************************************************************************
WB.Sheets(1).Name = "SalesData"
' ***************************************************************************
' Initialize the Worksheet Variable
' ***************************************************************************
Set WKS = WB.Sheets("SalesData")
' WB.Activate
' ***************************************************************************
' Add data to the worksheet - Note that the Range Statement Requires
' the WKS Reference both in Front of the Range and in front of the Cells
' statements.
' ***************************************************************************
WKS.Range(WKS.Cells(1, 1), WKS.Cells(2, 20)).Value = "Hello"
' ***************************************************************************
' Save the Workbook
' ***************************************************************************
WB.SaveAs FileName:="C:\Temp\Hello Rich2.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' ***************************************************************************
' Close the Workbook
' ***************************************************************************
WB.Close SaveChanges:=False
XL.Quit
Set WKS = Nothing
Set WB = Nothing
Set XL = Nothing
End Sub