There are a couple of issues. Because you have dimmed excelWorkBook as an
excel workbook object, you have forced "early binding". This may provide a
small performance advantage, but it will also set you up for some possible
problems. The reason is, you have bound the object model for the version of
excel you have installed. If another user has a different version, it can
cause errors or not run at all. The prefered method is to use "late
binding". That is to dim the object as generic objects. Then, when you set
them as excel objects, the version of Excel loaded on the computer running
the application will be loaded.
Also, you are starting one level too low. You first need to enstantiate the
Excel
Application. It is very import that all objects be fully qualified when
using Automation. If not done correctly, problems can arise. The one that is
common and very hard to figure out the first time is leaving an instance of
Excel running on your computer.
What happens is that if you use an unqualified reference, Access may not
know who the reference belongs. It will then start up another instance of
Excel to use. Now, when you Quit your application, the Quit will affect only
the instance you started, but the one Access started will still be running.
It will not show up in Task Manager Applications, but in the Processes tab.
So if you are testing your app and either it hangs trying to communicate with
Excel or you open Excel and it appears to hang, then look in Task Manager
Processes for an instance of Excel.exe.
Now here is how it is done.
First, here is some code you will need. Put it in a standard module by
itself:
****************************************************
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
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
*********************************************************
Private xlApp As Object ' Reference to Microsoft Excel.
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
Private blnExcelWasNotRunning As Boolean ' Flag for final release.
'Open Excel
On Error Resume Next ' Defer error trapping.
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.
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
Now it is important to shut it down completely and in the correct order.
The correct order is the reverse of how you defined them.
xlBook.Close
Set xlSheet = Nothing
Set xlBook = 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
Notice the if blnExcelWasNotRunning = False, that means Excel was already
open on the computer. That means the user had it open doing something so we
don't want stop it.
This is the guts of it and the hard part. All you need to remember from
here is to be sure you fully qualify all your objects. Did I say that twice?
Well then, it must be important.
