Link to home
Start Free TrialLog in
Avatar of shambalad
shambaladFlag for United States of America

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).
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

Open in new window

rptTAT.xls
Export-To-Excel-Problem.mdb
Avatar of shambalad
shambalad
Flag of United States of America image

ASKER

Added a post to question 23483481 requesting Stellan to post his answer here so that I may accept it and award him his earned points.
Todd
ASKER CERTIFIED SOLUTION
Avatar of StellanRosengren
StellanRosengren
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The empty sheet1 was the key. Changed routine to create new recordset each time.
Thanks for the help Stellan.
Todd
My pleasure. Thanks for the points.

Stellan