I am having a problem working with Excel objects from within an Access application. I have a form that sends data to an Excel workbook. This export process executes without error the first time around. All subsequent executions fail with an 'Object variable not set' (91). The only way to clear the error is to close out the Access aplication and restart it.
The problem appears to be with operations that work with ranges.
As near as I can tell, others have encountered this same error and asked about it in this forum, but I haven't seen a definitive answer as to why this is occurring and how to prevent it from happening.
I have attached a snippet of the actual subroutine where I an getting the error.
I have also attached an Access Database (.mdb) file. I tried to attach copy of the template (rptTAT.xlt) that the form uses, but apparently Excel templates (.xlt) are not allowed files for upload. In lieu, I uploaded a blank copy of the workbook. In order to run the application, you should first save the workbook as a template (.xlt).
I would be most grateful for any help on resolving this problem.
Private Function DeleteColumns( _
varDeleteColumns As Variant, _
xlsApp As Excel.application, _
xlsBook As Excel.Workbook, _
strWorksheet As String) As Boolean
Dim xlsSheet As Excel.Worksheet
Dim strProcedure As String
Dim intIndex As Integer
Dim strRange As String
Dim lngUBound As Long
Dim strMsg As String
10 On Error GoTo HandleError
20 strProcedure = "DeleteColumns"
' Excel objects must be open
30 If xlsBook Is Nothing Or _
xlsApp Is Nothing Then
40 GoTo ExitFunction
50 End If 'If xlsBook Is Nothing
70 Set xlsSheet = xlsBook.ActiveSheet
80 lngUBound = UBound(varDeleteColumns)
90 For intIndex = 0 To lngUBound
100 If Not IsEmpty(varDeleteColumns(intIndex)) Then
110 strRange = varDeleteColumns(intIndex)
' Error 91 - 'Object variable not set' on following line:
130 End If 'If Not IsEmpty(varDeleteColumns(intIndex))
140 Next intIndex 'For intIndex = 0 To lngUBound
150 DeleteColumns = True
160 Exit Function
170 DoCmd.Hourglass False
180 strMsg = "Module: " & strModule & NL & _
"Procedure: " & strProcedure & _
NL & "Error: " & _
Err.Description & _
" (" & Err.Number & ")" & NL & _
"Error Line: '" & Erl & "'" & NL & _
"Array Index: '" & intIndex & "'" & NL & _
"Range: '" & strRange & "'"
190 Debug.Print strMsg
200 MsgBox strMsg
210 Resume ExitFunction