Run time error 91 - Stuck! (1 Viewer)

[nsp27]

I've been working on this code for hours and changed many things and I'm still stuck. The code runs fine the first time then when I run it a second time I get the run time error-91......

I looked at many posts on this subject and have changed code but still can't figure it out.

Sub TieOutTemp()
Dim strCheck As String
Dim lngAddress As Long
Dim strCursor As String
Dim lngRow As Long
Dim rng As Range
Dim objXLTemp As Object
Dim objWBtemp As Object
 
Const strTieOutTempPath As String = "C:\RNETTDTieOut\BaseFiles\TieOutTemp.xls"
Const strRNETFilePath As String = "C:\RNETTDTieOut\BaseFiles\i000#accepted_items#age_st_fncurr.xls"
 
If FileExists(strRNETFilePath) Then
    FileCopy strRNETFilePath, strTieOutTempPath
End If
Set objXLTemp = CreateObject("Excel.Application")
objXLTemp.Workbooks.Open (strTieOutTempPath)
Set objWBtemp = objXLTemp.ActiveWorkbook
With objWBtemp
    strCheck = .Sheets(1).Cells(1, 1).Value
 
    If strCheck = "Best Buy" Then
        For x = 1 To 6
            .Sheets(1).Rows(x).Delete
        Next x
    End If
    .Sheets(1).Range("A1").Value = "Subtotal"
    .Sheets(1).Range("B1").Value = "Status"
    .Sheets(1).Range("C1").Value = "Carrier"
    .Sheets(1).Range("D1").Value = "State"
    .Sheets(1).Range("E1").Value = "Balance"
    .Sheets(1).Range("F1").Value = "Dollars"
    .Sheets(1).Range("F1").Value = "D/C"
    .Sheets(1).Range("G1").Value = "Dollars"
    .Sheets(1).Range("H1").Value = "TotalItems"
    .Sheets(1).Range("I1").Value = "Units"
    .Sheets(1).Range("J1").Value = "B/C"
 
    Set rng = .Sheets(1).Range("D1:D100").Find(What:="Carrier", _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
    If Not rng Is Nothing Then
        objXLTemp.Goto rng, True
 
    Else
        MsgBox "You need to add the current date to the Data Validation Summary MASTER on the Exiting Code....."
        GoTo Getout
    End If
 
    lngAddress = (ActiveCell.Row - 2) / 3
    strCursor = "D4"
    strCarrier = .Sheets(1).Range(strCursor).Value
 
    For x = 1 To lngAddress
        lngRow = (3 * x) + 1
        strCursor = "D" & lngRow
        .Sheets(1).Range(strCursor).Copy
        strRange = "C" & (lngRow - 2) & ":C" & (lngRow - 1) & ""
        .Sheets(1).Range(strRange).Select
        .ActiveSheet.Paste
    Next x
End With
 
Getout:
objWBtemp.Save
objWBtemp.Close
objXLTemp.Quit
Set objXLTemp = Nothing
Set objWBtemp = Nothing
End Sub

Any help would be appreciated!!!

Brad

 

[AccessMSSQL]

What line of code are you getting the error on? Error 91 is Object not set to an instance of an object, so it might be wherever you are setting your Excel objects.

Try changing the code where you open excel to this:

On Error Resume Next
Set objXLTemp = GetObject(, "Excel.Application")
If Err.Number > 0 Then  'Excel was not open
    Set objXLTemp = New Excel.Application
End If

 

[nsp27]

That worked, thank you so much!

Brad