You can't use the GetObject to refere to a specific Excel workbook file. The
object you either Get or Create (depending on whether an instance of the
application is already running) is the Excel Application object. You then
can open the file you want as a WorkBook object. Here is some sample code on
how it is done.
Be aware it is very important to carefully and explicitly reference your
Excel objects; otherwise, you will leave an instance of Excel running and it
will prevent the user from opening an Excel files at all. Should you
encounter this problem, it will not show up in the Applications tab of Task
Manager, but in the Processess tab.
It is also important that you release the references in the correct order to
avoid leaving the process running. Be sure you include releasing the objects
in your error handling as well. Even Closing Access will not destroy the
object. Notice in the last code example how the release of the objects is in
the procedure exit so that if the error handler is invoked, it is structured
to include releasing the objects.
Private xlApp As Object ' Reference to Microsoft Excel.
Private blnExcelWasNotRunning As Boolean ' Flag for final release.
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
'Open Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
--------------------------------
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
------------------------------------------
LoadAdjustedActuals_Exit:
'Close files and delete link to spreadsheet
On Error Resume Next
xlBook.Close
Set xlBook = Nothing
Set xlSheet = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlApp = Nothing
rstAccess.Close
Set rstAccess = Nothing
DoCmd.Hourglass False
Exit Sub
LoadAdjustedActuals_Err:
Select Case Err.Number
Case Is = 463
MsgBox "Lost Connection with Excel Spreadsheet" & vbNewLine & _
"Close and Re-Open Access", vbExclamation + vbOKOnly,
"ImportAdjusted Actuals"
Case Is = 2042
MsgBox "Invalid Data in Cell " & Chr(intColCount + 64) &
CStr(intRowCount) _
& vbNewLine & "Correct the Source Data and Re-run the Import", _
vbCritical + vbOKOnly, "Source Data Error"
Case Else
MsgBox Error$
End Select
Resume LoadAdjustedActuals_Exit
End Sub
