Access

Open Specific Excel File

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Justinlabenne

Description:

This procedure opens a specific Excel file from Access 

Discussion:

Open an Excel file that contains an Auto_Open event that may need refreshed after you update the Access database. Do this directly from Access. Since macros are enabled for Access, the Excel file being opened (if it contains macros) will skip the security check and run any open procedure. 

Code:

instructions for use

			

Option Compare Database Option Explicit Sub OpenSpecific_xlFile() ' Late Binding (Needs no reference set) Dim oXL As Object Dim oExcel As Object Dim sFullPath As String Dim sPath As String ' Create a new Excel instance Set oXL = CreateObject("Excel.Application") ' Only XL 97 supports UserControl Property On Error Resume Next oXL.UserControl = True On Error GoTo 0 ' Full path of excel file to open On Error GoTo ErrHandle sFullPath = CurrentProject.Path & "\TestFile.xls" ' Open it With oXL .Visible = True .Workbooks.Open (sFullPath) End With ErrExit: Set oXL = Nothing Exit Sub ErrHandle: oXL.Visible = False MsgBox Err.Description GoTo ErrExit End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Run it from a form button Private Sub cmdOpenExcelFIle_Click() ' Access form button Call OpenSpecific_xlFile End Sub

How to use:

  1. Open an Access Database
  2. Press Alt + F11
  3. From the Menu > Insert > Module
  4. Paste this code in
  5. Press Alt + Q to return to Access
 

Test the code:

  1. Assuming the code is assigned to a form button or some way of running it:
  2. Using the example provided:
  3. The Excel file is inside the same folder as this Access Database
  4. Press the form button, the specific Excel file will open
 

Sample File:

TestOpenExcel.zip 18.79KB 

Approved by mdmackillop


This entry has been viewed 444 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express