Run-time error 91: Object variable or With block variable not set

A

acrawford

Hello,

I have the following code in an Access 97 module to push data to an
Excel spreadsheet.

Private Sub DataPull()
Dim wrk As DAO.Workspace
Dim dbconn As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim msgoption As Long
Dim x As Integer
Dim xlws As Excel.Worksheet
Dim xlws2 As Excel.Worksheet
Dim xlrng As Excel.Range

Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "")
Set dbconn = wrk.OpenDatabase("Q:\Process Control Management\Process
Control Analysts\Antje\ops log1 97.mdb")
Set rs = dbconn.OpenRecordset("Select qry_ON_Open.*,
qry_ON_Department.Department from qry_ON_Open INNER JOIN
qry_ON_Department ON
qry_ON_Open.Change_Number=qry_ON_Department.[Change Number]")

msgoption = MsgBox("Do you want a PivotTable?", vbYesNoCancel, "Report
Type")

Select Case msgoption
Case vbYes
Set xlrng = xlws.Cells(4, 1)
'On Error Resume Next
xlrng.RemoveSubtotal
x = 1
For Each fld In rs.Fields
xlws.Cells(4, x).Value = fld.Name
x = x + 1
Next
Set xlrng = xlws.Cells(5, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
Set xlrng = xlws.Columns("D:D")
xlrng.NumberFormat = "$#,##0.00"
Set xlrng = xlws.Range(xlws.Cells(4, 1),
xlws.Cells(rs.RecordCount + 4, rs.Fields.Count))
Set xlws2 = ActiveWorkbook.Sheets.Add
xlws2.Name = "Pivot Table"

xlws2.PivotTableWizard xlDatabase, xlrng, xlws2.Cells(3, 1),
"Open Ops Notes", False, True, True, True, False, , True, True, , ,
True
xlws2.Cells(3, 1).Select
xlws2.PivotTables("Open Ops Notes").AddFields RowFields:="Ops
#"
With xlws2.PivotTables("Open Ops
Notes").PivotFields("Department")
.Orientation = xlDataField
.NumberFormat = "$#,##0.00"
End With
ActiveWorkbook.showpivottablefieldlist = False
Set xlws2 = Nothing
Case vbNo
Set xlrng = xlws.Cells(4, 1)
'On Error Resume Next
xlrng.RemoveSubtotal
x = 1
For Each fld In rs.Fields
xlws.Cells(4, x).Value = fld.Name
x = x + 1
Next
Set xlrng = xlws.Cells(5, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
Set xlrng = xlws.Columns("D:D")
xlrng.NumberFormat = "$#,##0.00"
Set xlrng = xlws.Range(xlws.Cells(4, 1),
xlws.Cells(rs.RecordCount + 4, rs.Fields.Count))
xlrng.Subtotal 2, xlSum, 4, True, False, xlSummaryAbove
xlws.Outline.ShowLevels 2
Case vbCancel
GoTo ExitStuff
End Select

ExitStuff:
Set xlws = Nothing
Set xlws2 = Nothing
Set xlrng = Nothing
rs.Close
Set fld = Nothing
Set rs = Nothing
dbconn.Close
Set dbconn = Nothing
Set wrk = Nothing
End Sub

I receive the run-time error 91: "Object variable or With block
variable not set" at the following line of code:
Set xlrng = xlws.Cells(4, 1)

What is causing the error?
Any help would be greatly appreciated.

TIA,
Regards,
Antje Crawford
 
D

Douglas J. Steele

The error is saying that nowhere have you instantiated xlws.

In other words, you don't have a statement

Set xlws = ....

anywhere prior to where you're trying to use it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello,

I have the following code in an Access 97 module to push data to an
Excel spreadsheet.

Private Sub DataPull()
Dim wrk As DAO.Workspace
Dim dbconn As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim msgoption As Long
Dim x As Integer
Dim xlws As Excel.Worksheet
Dim xlws2 As Excel.Worksheet
Dim xlrng As Excel.Range

Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "")
Set dbconn = wrk.OpenDatabase("Q:\Process Control Management\Process
Control Analysts\Antje\ops log1 97.mdb")
Set rs = dbconn.OpenRecordset("Select qry_ON_Open.*,
qry_ON_Department.Department from qry_ON_Open INNER JOIN
qry_ON_Department ON
qry_ON_Open.Change_Number=qry_ON_Department.[Change Number]")

msgoption = MsgBox("Do you want a PivotTable?", vbYesNoCancel, "Report
Type")

Select Case msgoption
Case vbYes
Set xlrng = xlws.Cells(4, 1)
'On Error Resume Next
xlrng.RemoveSubtotal
x = 1
For Each fld In rs.Fields
xlws.Cells(4, x).Value = fld.Name
x = x + 1
Next
Set xlrng = xlws.Cells(5, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
Set xlrng = xlws.Columns("D:D")
xlrng.NumberFormat = "$#,##0.00"
Set xlrng = xlws.Range(xlws.Cells(4, 1),
xlws.Cells(rs.RecordCount + 4, rs.Fields.Count))
Set xlws2 = ActiveWorkbook.Sheets.Add
xlws2.Name = "Pivot Table"

xlws2.PivotTableWizard xlDatabase, xlrng, xlws2.Cells(3, 1),
"Open Ops Notes", False, True, True, True, False, , True, True, , ,
True
xlws2.Cells(3, 1).Select
xlws2.PivotTables("Open Ops Notes").AddFields RowFields:="Ops
#"
With xlws2.PivotTables("Open Ops
Notes").PivotFields("Department")
.Orientation = xlDataField
.NumberFormat = "$#,##0.00"
End With
ActiveWorkbook.showpivottablefieldlist = False
Set xlws2 = Nothing
Case vbNo
Set xlrng = xlws.Cells(4, 1)
'On Error Resume Next
xlrng.RemoveSubtotal
x = 1
For Each fld In rs.Fields
xlws.Cells(4, x).Value = fld.Name
x = x + 1
Next
Set xlrng = xlws.Cells(5, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
Set xlrng = xlws.Columns("D:D")
xlrng.NumberFormat = "$#,##0.00"
Set xlrng = xlws.Range(xlws.Cells(4, 1),
xlws.Cells(rs.RecordCount + 4, rs.Fields.Count))
xlrng.Subtotal 2, xlSum, 4, True, False, xlSummaryAbove
xlws.Outline.ShowLevels 2
Case vbCancel
GoTo ExitStuff
End Select

ExitStuff:
Set xlws = Nothing
Set xlws2 = Nothing
Set xlrng = Nothing
rs.Close
Set fld = Nothing
Set rs = Nothing
dbconn.Close
Set dbconn = Nothing
Set wrk = Nothing
End Sub

I receive the run-time error 91: "Object variable or With block
variable not set" at the following line of code:
Set xlrng = xlws.Cells(4, 1)

What is causing the error?
Any help would be greatly appreciated.

TIA,
Regards,
Antje Crawford
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 1
I need to export a query to Excel pivot table 3
I need help with wrapping text in excel 2
Object variable or with Block variable not set 9
Runtime error 91: Object variable or With block variable not set 8
I need help with locking and Wrapping 0
I need help with locking selective cells in excel 3
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1

Top