|
|
|
|
|
|
|
|
|
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:
|
- Open an Access Database
- Press Alt + F11
- From the Menu > Insert > Module
- Paste this code in
- Press Alt + Q to return to Access
|
|
Test the code:
|
- Assuming the code is assigned to a form button or some way of running it:
- Using the example provided:
- The Excel file is inside the same folder as this Access Database
- Press the form button, the specific Excel file will open
|
|
Sample File:
|
TestOpenExcel.zip 18.79KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 351 times.
|
|
|