Automation Problem with Access 97/Excel 97
Hi there --
I have
VBA code in Access 97 which is opening an Automation object reference to
Excel 97. Even though I have code that is supposed to quit Excel and then set the
reference to nothing, I still have an instance of Excel listed as a running program
when I go into the Task Manager. The only way I can get it to go away is by
physically closing Access.
Here's the code I'm using. I've tried various permutations but so far haven't
been successful. Any suggestions would be appreciated.
Thanks in advance,
Stephen
Sub ExcelRpt()
On Error GoTo ErrHandler
Dim xlObj As Excel.Application
Err.Number = 0
On Error GoTo NotLoaded
Set xlObj = GetObject(, "Excel.Application")
NotLoaded:
If Err.Number = 429 Then
Set xlObj = CreateObject("Excel.Application")
intError = Err.Number
End If
xlObj.Visible = True
'Define the variable to hold the Excel file name and location.
strFileName = "C:\Audit\Audit.xls"
'Open the file and make it visible.
xlObj.Workbooks.Open strFileName
'Go to the Import spreadsheet. Clear the old data and position the
'cell point at A2 for the new data.
Worksheets("Import").Activate
xlObj.Goto Worksheets("Import").Range("A2")
Set rng = ActiveCell.CurrentRegion
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count).Select
Selection.Clear
Set db = CurrentDb()
'The strSQL statement string comes from a global variable
Set rs = db.OpenRecordset(strSQL)
'Insert the records into the spreadsheet.
'Get the total number of records in the query.
rs.MoveLast
rs.MoveFirst
intCount = rs.RecordCount
'Set the initial values for the row and column coordinates.
x = 2 'Initial row value
y = 1 'Initial column value
'Loop through all the fields in all the records, obtain the values, and
'insert them into the spreadsheet.
xlObj.DisplayAlerts = False
For y = 1 To (rs.Fields.Count - 1)
For i = 1 To intCount
xlObj.Worksheets("Import").Cells(x, y).Value = rs.Fields(y).Value
If rs.EOF = False Then
rs.MoveNext
End If
x = x + 1
Next i
x = 2
rs.MoveFirst
Next y
'Enter the total number of records for this audit.
'xlObj.Range("AuditRecordCount").Value = (ActiveCell.CurrentRegion.Rows.Count - 1)
'Create range names for the fields.
xlObj.Goto Worksheets("Import").Range("A2")
Set rng = Nothing
Set rng = ActiveCell.CurrentRegion
rng.Select
Selection.CreateNames Top:=True
ExitSub:
xlObj.ActiveWorkbook.Save
xlObj.ActiveWorkbook.Close
If intError = 429 Then
xlObj.Quit
Err.Number = 0
End If
Set rs = Nothing
Set db = Nothing
Set xlObj = Nothing
Exit Sub
ErrHandler:
If Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description
End If
xlObj.ActiveWorkbook.Close SaveChanges:=False
xlObj.Quit
Set rs = Nothing
Set db = Nothing
Set xlObj = Nothing
End Sub
