D
dailem
I have written a section of code that extracts all data from a
worksheet & puts it into a new pivot table. The code works every other
time that I run it, which is strange. I ran into a similar problem
some time ago that I was able to fix by setting object variables to
nothing at the end of my code, however that doesn't seem to work here.
Below is the code:
Dim xlWb As Object
Dim xlWs As Object
Dim xlAp As Object
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Sheets("Sheet3")
a = xlWs.Range("A1").Address
lastcell = xlWs.Range("A1").SpecialCells(xlCellTypeLastCell).Address
MyRange = xlWs.Name & "!" & a & ":" & lastcell
With xlWb
'Add Table & cross fingers
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=MyRange) _
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With
'Move desired column headings to correct sections on pivot table report
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField2)
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField3)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
'Close Pivot Table Menus
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
'release excel objects after all activity
'******
Set xlWs = Nothing
Set xlWb = Nothing
Set objXl = Nothing
Set objSht = Nothing
....one other bit of info since I didn't include all of the code. The
variables 'strField, strField2, strField3' are defined as follows:
strField = objXl.Selection.Cells(1, 1).Text
strField2 = objXl.Selection.Cells(1, 7).Text
strField3 = objXl.Selection.Cells(1, 6).Text
....where 'objXl' is a New Excel.Application
I think this may be a problem area even though I'm setting it to =
nothing at the end of the code, but heck, I don't know. Any ideas as
to why this works exactly 1/2 of the time would be greatly appreciated.
worksheet & puts it into a new pivot table. The code works every other
time that I run it, which is strange. I ran into a similar problem
some time ago that I was able to fix by setting object variables to
nothing at the end of my code, however that doesn't seem to work here.
Below is the code:
Dim xlWb As Object
Dim xlWs As Object
Dim xlAp As Object
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Sheets("Sheet3")
a = xlWs.Range("A1").Address
lastcell = xlWs.Range("A1").SpecialCells(xlCellTypeLastCell).Address
MyRange = xlWs.Name & "!" & a & ":" & lastcell
With xlWb
'Add Table & cross fingers
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=MyRange) _
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With
'Move desired column headings to correct sections on pivot table report
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField2)
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strField3)
.Orientation = xlDataField
.Position = 1
.Function = xlSum
End With
'Close Pivot Table Menus
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
'release excel objects after all activity
'******
Set xlWs = Nothing
Set xlWb = Nothing
Set objXl = Nothing
Set objSht = Nothing
....one other bit of info since I didn't include all of the code. The
variables 'strField, strField2, strField3' are defined as follows:
strField = objXl.Selection.Cells(1, 1).Text
strField2 = objXl.Selection.Cells(1, 7).Text
strField3 = objXl.Selection.Cells(1, 6).Text
....where 'objXl' is a New Excel.Application
I think this may be a problem area even though I'm setting it to =
nothing at the end of the code, but heck, I don't know. Any ideas as
to why this works exactly 1/2 of the time would be greatly appreciated.