troubleshooting Question

Run-time error 91 Every Other Execution

Avatar of cekendricks
cekendricks asked on
Microsoft AccessMicrosoft Excel
3 Comments1 Solution414 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros