shambalad
asked on
Export to Excel - Object variable not set error (91) when creating workbook second time (Part II)
This question is related to question 23483481 (https://www.experts-exchange.com/questions/23483481/Object-variable-not-set-error-91-when-creating-workbook-2nd-time-1st-time-works.html?anchorAnswerId=21781439#a21781439).
There were two questions answered in question 23483481. EE Expert 'StellanRosengren' answered both of those question. This is a breakout of the second question so that Stellan can get credit for both answers.
This question has to do with 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 the form and re-open it.
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).
Export-To-Excel-Problem.mdb
There were two questions answered in question 23483481. EE Expert 'StellanRosengren' answered both of those question. This is a breakout of the second question so that Stellan can get credit for both answers.
This question has to do with 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 the form and re-open it.
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).
Public Function fnSetDataRange( _
xlsApp As Excel.application, _
strRange As String, _
xlsSheet As Excel.Worksheet, _
Optional strFirstCell As String, _
Optional strLastCell As String) _
As Boolean
' Sets range of data loaded to worksheet
' Default assumes that first row has no headings,
' hence default first cell is "$A$1".
' If strFirstCell is passed, use that.
Dim rngLastColumn As Excel.Range
Dim rngFirstRow As Excel.Range
Dim rngLastCell As Excel.Range
Dim rngLastRow As Excel.Range
Dim strProcedure As String
Dim strSheet As String
Dim strMsg As String
10 On Error GoTo HandleError
20 strProcedure = "fnSetDataRange"
30 strSheet = xlsSheet.name
40 If strFirstCell = "" Then
50 strFirstCell = "$A$1"
60 End If 'If strFirstCell = ""
'********************************************************************
'**** Error 91 'Object not set occurs on this line: <=========
70 Set rngLastColumn = xlsSheet.Cells.Find("*", _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious _
).EntireColumn
'***********************************************************************
80 Set rngLastRow = xlsSheet.Cells.Find("*", _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious _
).EntireRow
90 Set rngLastCell = xlsApp.Intersect( _
rngLastColumn, _
rngLastRow)
100 rngLastCell.Activate
110 strLastCell = xlsApp.ActiveCell.Address
' Need to place cursor in A:1 prior
' to adding the range in order to
' avoid an off-set error
120 xlsSheet.Range("A1").Select
130 xlsApp.Names.Add name:=strRange, _
RefersTo:=xlsApp.Selection.Range( _
strFirstCell, _
strLastCell)
140 fnSetDataRange = True
ExitFunction:
150 Exit Function
HandleError:
160 strMsg = "Module: " & strModule & NL & _
"Procedure: " & strProcedure & NL & _
"Error: " & Err.Description & _
" (" & Err.Number & ")" & NL & _
"Error Line: " & Erl
170 MsgBox strMsg
180 Resume ExitFunction
End Function
rptTAT.xlsExport-To-Excel-Problem.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The empty sheet1 was the key. Changed routine to create new recordset each time.
Thanks for the help Stellan.
Todd
Thanks for the help Stellan.
Todd
My pleasure. Thanks for the points.
Stellan
Stellan
ASKER
Todd