Open an Excel File From Within Access Using Object Method, Modify and Save It
It can be useful to open 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 using the older OBJECT method. 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
Private XL As Object ' Excel.Application
Private WB As Object ' Excel.Workbook
Private WKS As Object ' Excel.Worksheet
' ***************************************************************************
' This method of opening Excel workbooks through Microsoft Access
' Uses the older object model
'
' In the Visual Basic Editor, Select from the Menu Bar:
' Tools, References, and then put a checkmark in the
' Microsoft Excel Object Library
' ***************************************************************************
Public Sub OpenExcelFileFromAccess()
' ***************************************************************************
' Create an Excel Instance With No Workbooks Yet
' ***************************************************************************
Set XL = CreateObject("Excel.Application")
' ***************************************************************************
' Add a Template and create a workbook variable
' Note that the Workbooks.Add can be used to create a new empty
' workbook or a copy of a "Template" by adding a path to an existing
' workbook.
' ***************************************************************************
Set WB = XL.Workbooks.Add("C:\Temp\Hello Rich2.xlsm")
' ***************************************************************************
' Optional: Make it Visible
' ***************************************************************************
XL.Visible = True
' ***************************************************************************
' Rename the worksheet
' ***************************************************************************
WB.Sheets(1).Name = "NewWorksheet"
' ***************************************************************************
' Initialize (Set) the Worksheet Variable
' ***************************************************************************
Set WKS = WB.Worksheets("NewWorksheet")
' ***************************************************************************
' 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 Rich5.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