Working with Excel from Access is not that difficult, but there are a couple
of things important to know. First, here is some sample code to open and
Excel file and select a specific worksheet:
___________________________________________________________
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)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
_________________________________________________________
Here is the code you would use to close the excel file and destroy the
object references to it:
________________________________________________________________
'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
__________________________________________________________
You will also need this code. It should go in a Standard module of its own:
Option Compare Database
Option Explicit
' 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
__________________________________________________________
Now to get values from specific cells:
SomeVariable = xlsheet.range("B7").value
__________________________________________________________
Note that in your case, where you are going to be reading values from 800
spreadsheets, it won't be necessary to destroy the xlApp object after each
sheet. You can leave the instance of Excel running and just close the
workbook and open another one.
Now, for the detail stuff. The code above is very careful to fully qualify
all object references and to be sure that when done, all object references
have been destroyed. When working with Excel from Access, this is important.
If you do not fully qualify your references, Access can get confused and not
know which instances of Excel you are using. What it then does is create its
own instance of Excel, not the one you instanciated. All will seem to be
fine, but then you try to run Excel and it hangs up. Oops! so you go to Task
Manager and look at the Application tab, but Excel is not there. Nope, you
will find it still running in the Processes tab. That is because you Quit
the instance you created, but the instance Access created is still running.
This can also happen if you error out and don't use good error handling to
ensure you have closed Excel down properly.
Notice also that the xlApp, xlBook, and xlSheet object references are Dimmed
as Objects. If you Dim them as Excel objects, that is called Early Binding
and binds the currently installed version of Excel into your application. If
you have a user that is not on the same version of Excel, this can cause run
time errors. The technique used here is called Late Binding. Since the
Excel application is not specified until run time, it doesn't matter what
version of Excel the user is using, because it binds the version the user has
at run time.
This seems like a lot of code, but it is necessary to handle Excel properly.
Good Luck
