Avatar of cekendricks
 asked on

Run-time error 91 Every Other Execution

I have a small piece of code that writes the results of a query to an excel spreadsheet and then performs some minor formatting of the spreadsheet via a second sub-procedure.  The behavior that I am seeing is that every other time I run the main procedure it works just fine, but the other times it fails with "Run-time error 91  Object variable or With block variable not set".  In checking other message boards, some have determined that the Excel application wasn't closed properly.  Does not the line 'Set xlApp = Nothing' accomplish that task?  Oh!..The line that is highlighted when it fails is the very first line of the CreateStyle() sub procedure: ActiveWorkbook.Styles.Add "cek1"

Public Sub ExportOrders()

    Dim xlApp As Excel.Application
    'Dim xlApp As Object
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    Dim xlRng As Excel.Range
    Dim xlRng2 As Excel.Range
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim x As Integer, y As Integer, z As Integer
    Dim strSql As String
    Dim strSt As String

    'Set xlApp = CreateObject("Excel.Application")
    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
    Set db = CurrentDb
    strSt = "CA"
    strSql = "SELECT OrderNumber, BillingFirstName, BillingLastName, BillingAddress1, " & _
             "BillingCity, BillingState, BillingZip FROM tblOrders WHERE BillingState = ""CA"""
    Debug.Print strSql
    Set rs = db.OpenRecordset(strSql)
    xlApp.Visible = True
    Set xlRng = xlWs.Cells(4, 1)
    x = 4
    While Not rs.EOF
        For y = 1 To rs.Fields.Count
            xlRng.Cells(x, y) = rs.Fields(y - 1).Value
        Next y
        x = x + 1
    Set xlRng = xlWs.Range(xlWs.Cells(4, 1), xlWs.Cells.SpecialCells(xlCellTypeLastCell))
    Set xlRng2 = xlRng.Columns(7)
    Call CreateStyle
    xlRng2.Style = "cek1"
    Set xlApp = Nothing
    Set xlWb = Nothing
End Sub
Public Sub CreateStyle()
    ActiveWorkbook.Styles.Add "cek1"
    With ActiveWorkbook.Styles("cek1")
        .Font.Bold = True
        .Font.Size = 16
        .Font.ColorIndex = 28
    End With
End Sub
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Jeffrey Coachman

Not sure, but I think you have to close/quit the Excel instance also

    Set xlApp = Nothing
    Set xlWb = Nothing
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

Sorry, that CopyFromRecordset reference should have been:

xlWs.range("A4").CopyFromRecordset rs
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck