Solved Error number :91 = Object variable or With variable not set (1 Viewer)

[Leo_Polla_Psemata]

Hi
I get this error when i try to play with this code

Error number :91 = Object variable or With variable not set

The purpose, one very simple table consisted of four fields, i just want to get these fields exported in an excel sheet.

Private Sub Btn1_Click()
On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim rsBS As DAO.Recordset
    Dim i As Integer
 
    'Show user work is being performed
    DoCmd.Hourglass (True)
 
    '*********************************************
    '              RETRIEVE DATA
    '*********************************************
    'SQL statement to retrieve data from database

     SQL = "SELECT Forma1.IDM, Forma1.bk, Forma1.freight, Forma1.curre " & _
        "FROM Forma1;"


    '*********************************************
    '             BUILD SPREADSHEET
    '*********************************************
    'Create an instance of Excel and start building a spreadsheet
 
    'Early Binding
    Set xlApp = Excel.Application
 
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    Range("A4").Select
    ActiveWindow.FreezePanes = True
' Here I try to remove grid
    xlSheet.Activate
    ActiveWindow.DisplayGridlines = False

With xlSheet
        .Name = "IMPORT BLss"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 10

        'Format Labels
        .Range("A1").Value = "ID"
        .Range("B1").Value = "BK"
        .Range("C1").Value = "FREIGHT"
        .Range("D1").Value = "CURRENCY"

        'provide initial value to row counter
        i = 2
        'Loop through recordset and copy data from recordset to sheet
        Do While Not rsBS.EOF

    .Range("A" & i).Value = Nz(rsBS!IDM, "")
    .Range("B" & i).Value = Nz(rsBS!bk, "")
    .Range("C" & i).Value = Nz(rsBS!freight, "")
    .Range("D" & i).Value = Nz(rsBS!curre, "")

            i = i + 1
            rsBS.MoveNext

            Loop

End With

SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    rsBS.Close
    Set rsBS = Nothing
    Exit Sub
  
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit

End Sub

 

[Gasman]

Perhaps walk though your code and see if xlBook and xlSheet actually get created?

 

[Leo_Polla_Psemata]

Yes, the code create the xlBook and xlSheet, however cursor stops/freezes at A2 cell , as if it cannot run the
Do While Not rsBS.EOF ....loop part of the line

 

[theDBguy]

Hi. I don't see where you opened your recordset.

 

[The_Doc_Man]

You DIM'd rsBS but never actually set it to anything. Therefore, rsBS.EOF is, in effect, <nothing>.EOF - thus error 91.

 

[Gasman]

Well there you go......
You have pinpointed the error, which TBH is not the 'normal' one I've seen when doing this.

However, always wise to walk though your code when errors happen. Even do it when they do not, so as to see the code is doing as opposed to what you think it is doing.

 

[Leo_Polla_Psemata]

Many thanks
Now it works, no error

I will open a new thread to ask the second question about how can someone format the field "freight" to USD or Eur, based on the field curre.

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim RSBS As DAO.Recordset
    Dim i As Integer
 
    'Show user work is being performed
    DoCmd.Hourglass (True)
 
    '*********************************************
    '              RETRIEVE DATA
    '*********************************************
    'SQL statement to retrieve data from database

     SQL = "SELECT Forma1.ID, Forma1.bk, Forma1.freight, Forma1.curre " & _
        "FROM Forma1;"

    'Execute query and populate recordset
    Set RSBS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)


    'If no data, don't bother opening Excel, just quit
    If RSBS.RecordCount = 0 Then
        MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
        GoTo SubExit
    End If


    '*********************************************
    '             BUILD SPREADSHEET
    '*********************************************
    'Create an instance of Excel and start building a spreadsheet
 
    'Early Binding
    Set xlApp = Excel.Application
 
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    xlSheet.Activate
    ActiveWindow.DisplayGridlines = False


With xlSheet
        .Name = "IMPORT BLss"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 10

        'Format Labels
        .Range("A1").Value = "ID"
        .Range("B1").Value = "BK"
        .Range("C1").Value = "FREIGHT"
        .Range("D1").Value = "CURRENCY"

        'provide initial value to row counter
        i = 2
        'Loop through recordset and copy data from recordset to sheet
        Do While Not RSBS.EOF

    .Range("A" & i).Value = Nz(RSBS!ID, "")
    .Range("B" & i).Value = Nz(RSBS!bk, "")
    .Range("C" & i).Value = Nz(RSBS!freight, "")
    .Range("D" & i).Value = Nz(RSBS!curre, "")

            i = i + 1
            RSBS.MoveNext

            Loop

End With

SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    RSBS.Close
    Set RSBS = Nothing
    Exit Sub
 
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit