Open an Excel File From Within Access, Modify and Save It
Opening an existing Excel workbook from within Access can be a useful tool to create reports, update Workbooks, and enhance the user experience.
The code provided here provides two methods to open a workbook from within Microsoft Access
:
(1) Use a hard-coded path to the workbook to be opened
(2) Present the user with a file open dialog box in which they can navigate to the file to be opened
Method 1 requires one Object Library to be added to the Access application:
(1)
Microsoft Excel Object Library
Method 2 requires two Object Libraries to be added to the Access application:
(1) Microsoft Excel Object Library
(2) Microsoft Office Object Library
These libraries are inserted into the code from the Visual Basic Editor's menu bar: Tools, References.
Put a checkbox on the libraries to include.
Program Code
' *****************************************************************************************************
' E X A M P L E 1 - H A R D C O D E D P A T H T O O P E N E X C E L W O R K B O O K
' *****************************************************************************************************
Option Compare Database
Option Explicit
Public Sub OpenExcelWorkbookFromAccess()
' ***************************************************************************
' From within Access, Open An Excel Workbook, Update the Workbook
' Contents, and then Save the Workbook
' ***************************************************************************
' ***************************************************************************
' In the VBE (Visual Basic Editor of Access), in Tools, References
' Include:
' 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.Open("C:\Temp\Hello Rich2.xlsm")
' ***************************************************************************
' Rename the first Worksheet
' ***************************************************************************
WB.Sheets(1).Name = "NewSheetName"
' ***************************************************************************
' Initialize the Worksheet Variable
' ***************************************************************************
Set WKS = WB.Sheets("NewSheetName")
' 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 = "NewValues"
' ***************************************************************************
' Save the Workbook
' ***************************************************************************
WB.SaveAs FileName:="C:\Temp\Hello Rich3.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
' *****************************************************************************************************
' E X A M P L E 2 - U S E R D I A L O G P A T H T O O P E N E X C E L W O R K B O O K
' *****************************************************************************************************
Option Compare Database
Option Explicit
Dim strFilePathToWorkbook As String
Public Sub OpenExcelWorkbookFromAccess()
' ***************************************************************************
' From within Access, Open An Excel Workbook, Update the Workbook
' Contents, and then Save the Workbook
' Use the File Open Dialog Box provided in the Office Object Library
' ***************************************************************************
' ***************************************************************************
' In the VBE (Visual Basic Editor of Access), in Tools, References
' Include:
' Microsoft Excel Object Library
' Microsoft Office 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
' ***************************************************************************
' Ask The User to Pick a Workbook to Open
' ***************************************************************************
Call GetWorkbookNameToOpen
If strFilePathToWorkbook = "" Then
MsgBox ("No File Selected")
Exit Sub
End If
' ***************************************************************************
' Add a Workbook and Worksheets To The Excel Instance
' ***************************************************************************
Set WB = XL.Workbooks.Open(strFilePathToWorkbook)
' ***************************************************************************
' Rename the first Worksheet
' ***************************************************************************
WB.Sheets(1).Name = "NewSheetName"
' ***************************************************************************
' Initialize the Worksheet Variable
' ***************************************************************************
Set WKS = WB.Sheets("NewSheetName")
' 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 = "NewValues2"
' ***************************************************************************
' Save the Workbook
' ***************************************************************************
WB.SaveAs FileName:="C:\Temp\Hello Rich4.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
Private Sub GetWorkbookNameToOpen()
Dim dlgOpenFile As FileDialog
strFilePathToWorkbook = ""
Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
With dlgOpenFile
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel 2007-2013 Workbook", "*.xlsm"
.Filters.Add "Excel 2007-2013 Workbook", "*.xlsx"
.Filters.Add "Excel 2003 Workbook", "*.xls"
.FilterIndex = 1
.Show
If .SelectedItems.Count < 1 Then
Exit Sub
End If
strFilePathToWorkbook = .SelectedItems(1)
End With
End Sub