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")
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")
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
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")
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")
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