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.Applic ation")
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
xlWs.Select
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
rs.MoveNext
Wend
Set xlRng = xlWs.Range(xlWs.Cells(4, 1), xlWs.Cells.SpecialCells(xl CellTypeLa stCell))
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("cek 1")
.Font.Bold = True
.Font.Size = 16
.Font.ColorIndex = 28
End With
End Sub
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.Applic
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
xlWs.Select
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
rs.MoveNext
Wend
Set xlRng = xlWs.Range(xlWs.Cells(4, 1), xlWs.Cells.SpecialCells(xl
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("cek
.Font.Bold = True
.Font.Size = 16
.Font.ColorIndex = 28
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, that CopyFromRecordset reference should have been:
xlWs.range("A4").CopyFromR ecordset rs
xlWs.range("A4").CopyFromR
xlApp.quit
Set xlApp = Nothing
Set xlWb = Nothing