Automation error

[overseer07]

I'm exporting data from Access into Excel and reformatting, including adding
a pivot table based off of one of the worksheets in the report. Everything
works perfectly the first time the code is run, but any subsequent attempts
result in:
Run-time error '1004':
Method 'Sheets' of object '_Global' failed.

The break occurs at objWkb.PivotCaches.Add... in the code below.

I know just enough about programming to not know what I'm doing, so any help
in resolving this would be appreciated.

I've left declarations, initializations and error checks out for
legibility's sake.

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
objSht.Name = "Sheetname"
'copy the query to the new spreadsheet
.Range(.Cells(4, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset
rs
'reformat to match the current report
...
.basic formatting code
...
End With
End With

objWkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheetname!R3C1:R" & intLastRow - 1 & "C20").CreatePivotTable
TableDestination:= _
"[Book1]Sheet3!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
Set objSht = objWkb.Worksheets(3)
With objSht.PivotTables("PivotTable1").PivotFields( _
"Data Conversion Target Date")
.Orientation = xlRowField
.Position = 1
End With
objSht.PivotTables("PivotTable1").AddDataField objSht.PivotTables( _
"PivotTable1").PivotFields("# parts options"), "Count of Est. # of
Part.", _
xlCount
objSht.PivotTables("PivotTable1").PivotFields("Count of Est. # of
Part."). _
Function = xlSum
objSht.PivotTables("PivotTable1").AddDataField objSht.PivotTables( _
"PivotTable1").PivotFields("Plan Name"), "Count of Plan Name", xlCount
objSht.Name = "Pivot Table"

 

[Ralph]

I would run the code, then check task manager to see if an instance of Excel
is still running. If that is the case then check this web site:

http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

I'm just guessing but if Excel is not closing then you may need to change
the way you refer to Sheetname when you create your pivot table. Something
like:

objWkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
objSht &"!R3C1:R" & intLastRow - 1 & "C20").CreatePivotTable

 

[overseer07]

That was the problem. I've changed how I'm referencing the datasheet and it
runs smoothly now.

Thanks Ralph!

I would run the code, then check task manager to see if an instance of Excel
is still running. If that is the case then check this web site:

http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

I'm just guessing but if Excel is not closing then you may need to change
the way you refer to Sheetname when you create your pivot table. Something
like:

objWkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
objSht &"!R3C1:R" & intLastRow - 1 & "C20").CreatePivotTable

I'm exporting data from Access into Excel and reformatting, including adding
a pivot table based off of one of the worksheets in the report. Everything
works perfectly the first time the code is run, but any subsequent attempts
result in:
Run-time error '1004':
Method 'Sheets' of object '_Global' failed.

The break occurs at objWkb.PivotCaches.Add... in the code below.

I know just enough about programming to not know what I'm doing, so any help
in resolving this would be appreciated.

I've left declarations, initializations and error checks out for
legibility's sake.

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
objSht.Name = "Sheetname"
'copy the query to the new spreadsheet
.Range(.Cells(4, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset
rs
'reformat to match the current report
...
.basic formatting code
...
End With
End With

objWkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheetname!R3C1:R" & intLastRow - 1 & "C20").CreatePivotTable
TableDestination:= _
"[Book1]Sheet3!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
Set objSht = objWkb.Worksheets(3)
With objSht.PivotTables("PivotTable1").PivotFields( _
"Data Conversion Target Date")
.Orientation = xlRowField
.Position = 1
End With
objSht.PivotTables("PivotTable1").AddDataField objSht.PivotTables( _
"PivotTable1").PivotFields("# parts options"), "Count of Est. # of
Part.", _
xlCount
objSht.PivotTables("PivotTable1").PivotFields("Count of Est. # of
Part."). _
Function = xlSum
objSht.PivotTables("PivotTable1").AddDataField objSht.PivotTables( _
"PivotTable1").PivotFields("Plan Name"), "Count of Plan Name", xlCount
objSht.Name = "Pivot Table"