Debra Dalgleish: Pivot Table problem

G

Guest

I recorded a macro creating and formatting a pivot table.
Created a command Button with an On_Click event
When the code runs, it fails at the indicated point below.
MsgBox shows that there is no seriescollection, even though, obviously,
there was when the macro was recorded. Unchanged data source.

Must have something to do with the ApplyCustomType.

Any Ideas?
Thanks for the help!

Code Below

Private Sub CommandButton1_Click()
'
' Chart Macro
' Macro recorded 5/19/2005 by Lee Hunter
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim MyWkBk As String, MyWkSht As String, Tabledef As String
MyWkBk = ActiveWorkbook.Name
MyWkSht = ActiveWorkbook.Worksheets(1).Name
Tabledef = "[" & MyWkBk & "]" & MyWkSht & "!R3C3"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;DefaultDir=S:\1310\Quality\Metrics;DriverId=25;FIL=MS Access;MaxBuf" _
), Array("ferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT ArrChtInp.Trip, ArrChtInp.Stat, ArrChtInp.`Num of Occ`" &
Chr(13) & "" & Chr(10) & "FROM `S:\1310\Quality\Metrics\Trip`.ArrChtInp
ArrChtInp" & Chr(13) & "" & Chr(10) & "ORDER BY ArrChtInp.Trip" _
)
.CreatePivotTable TableDestination:=Tabledef, TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Trip", _
ColumnFields:="Stat"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Num of
Occ").Orientation = _
xlDataField
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Columns with Depth"
MsgBox ActiveChart.SeriesCollection.Count ***** Now shows 0 ******
************ ActiveChart.SeriesCollection(2).Select ****** Fails here
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub
 
D

Debra Dalgleish

You don't specify the chart's source data in the code, e.g.:

Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").PivotTables(1).TableRange1

Or, see Jon Peltier's code samples for creating charts:

http://www.peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Lee said:
I recorded a macro creating and formatting a pivot table.
Created a command Button with an On_Click event
When the code runs, it fails at the indicated point below.
MsgBox shows that there is no seriescollection, even though, obviously,
there was when the macro was recorded. Unchanged data source.

Must have something to do with the ApplyCustomType.

Any Ideas?
Thanks for the help!

Code Below

Private Sub CommandButton1_Click()
'
' Chart Macro
' Macro recorded 5/19/2005 by Lee Hunter
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim MyWkBk As String, MyWkSht As String, Tabledef As String
MyWkBk = ActiveWorkbook.Name
MyWkSht = ActiveWorkbook.Worksheets(1).Name
Tabledef = "[" & MyWkBk & "]" & MyWkSht & "!R3C3"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;DefaultDir=S:\1310\Quality\Metrics;DriverId=25;FIL=MS Access;MaxBuf" _
), Array("ferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT ArrChtInp.Trip, ArrChtInp.Stat, ArrChtInp.`Num of Occ`" &
Chr(13) & "" & Chr(10) & "FROM `S:\1310\Quality\Metrics\Trip`.ArrChtInp
ArrChtInp" & Chr(13) & "" & Chr(10) & "ORDER BY ArrChtInp.Trip" _
)
.CreatePivotTable TableDestination:=Tabledef, TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Trip", _
ColumnFields:="Stat"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Num of
Occ").Orientation = _
xlDataField
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Columns with Depth"
MsgBox ActiveChart.SeriesCollection.Count ***** Now shows 0 ******
************ ActiveChart.SeriesCollection(2).Select ****** Fails here
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub
Click to expand...
 
G

Guest

Thanks for the prompt reply, Debra. It's really appreciated.

I'll add that bit to my code.

Why do you suppose that code did not show up when I recorded the macro?
Lee

Debra Dalgleish said:
You don't specify the chart's source data in the code, e.g.:

Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").PivotTables(1).TableRange1

Or, see Jon Peltier's code samples for creating charts:

http://www.peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Lee said:
I recorded a macro creating and formatting a pivot table.
Created a command Button with an On_Click event
When the code runs, it fails at the indicated point below.
MsgBox shows that there is no seriescollection, even though, obviously,
there was when the macro was recorded. Unchanged data source.

Must have something to do with the ApplyCustomType.

Any Ideas?
Thanks for the help!

Code Below

Private Sub CommandButton1_Click()
'
' Chart Macro
' Macro recorded 5/19/2005 by Lee Hunter
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim MyWkBk As String, MyWkSht As String, Tabledef As String
MyWkBk = ActiveWorkbook.Name
MyWkSht = ActiveWorkbook.Worksheets(1).Name
Tabledef = "[" & MyWkBk & "]" & MyWkSht & "!R3C3"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;DefaultDir=S:\1310\Quality\Metrics;DriverId=25;FIL=MS Access;MaxBuf" _
), Array("ferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT ArrChtInp.Trip, ArrChtInp.Stat, ArrChtInp.`Num of Occ`" &
Chr(13) & "" & Chr(10) & "FROM `S:\1310\Quality\Metrics\Trip`.ArrChtInp
ArrChtInp" & Chr(13) & "" & Chr(10) & "ORDER BY ArrChtInp.Trip" _
)
.CreatePivotTable TableDestination:=Tabledef, TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Trip", _
ColumnFields:="Stat"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Num of
Occ").Orientation = _
xlDataField
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Columns with Depth"
MsgBox ActiveChart.SeriesCollection.Count ***** Now shows 0 ******
************ ActiveChart.SeriesCollection(2).Select ****** Fails here
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub
Click to expand...
Click to expand...
 
D

Debra Dalgleish

You're welcome. I don't know why it doesn't include the source in the
recorded code. However, if the active cell is in the pivot table range,
the code should run correctly, without the source range being specified.

When you tried to run your recorded code, you must have had a different
cell selected, and that's when you saw the error.

Lee said:
Thanks for the prompt reply, Debra. It's really appreciated.

I'll add that bit to my code.

Why do you suppose that code did not show up when I recorded the macro?
Lee

:

You don't specify the chart's source data in the code, e.g.:

Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").PivotTables(1).TableRange1

Or, see Jon Peltier's code samples for creating charts:

http://www.peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Lee said:
I recorded a macro creating and formatting a pivot table.
Created a command Button with an On_Click event
When the code runs, it fails at the indicated point below.
MsgBox shows that there is no seriescollection, even though, obviously,
there was when the macro was recorded. Unchanged data source.

Must have something to do with the ApplyCustomType.

Any Ideas?
Thanks for the help!

Code Below

Private Sub CommandButton1_Click()
'
' Chart Macro
' Macro recorded 5/19/2005 by Lee Hunter
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim MyWkBk As String, MyWkSht As String, Tabledef As String
MyWkBk = ActiveWorkbook.Name
MyWkSht = ActiveWorkbook.Worksheets(1).Name
Tabledef = "[" & MyWkBk & "]" & MyWkSht & "!R3C3"
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;DefaultDir=S:\1310\Quality\Metrics;DriverId=25;FIL=MS Access;MaxBuf" _
), Array("ferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT ArrChtInp.Trip, ArrChtInp.Stat, ArrChtInp.`Num of Occ`" &
Chr(13) & "" & Chr(10) & "FROM `S:\1310\Quality\Metrics\Trip`.ArrChtInp
ArrChtInp" & Chr(13) & "" & Chr(10) & "ORDER BY ArrChtInp.Trip" _
)
.CreatePivotTable TableDestination:=Tabledef, TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Trip", _
ColumnFields:="Stat"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Num of
Occ").Orientation = _
xlDataField
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Columns with Depth"
MsgBox ActiveChart.SeriesCollection.Count ***** Now shows 0 ******
************ ActiveChart.SeriesCollection(2).Select ****** Fails here
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub
Click to expand...
Click to expand...
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

Run macro on refresh data (pivot table) 0
Format Data Series in a Pivot Chart 2
macro for pivot table 1
change in the formatting of data series 2
Another odd VBA glitch in 2007 4
Looping through charts in a worksheet 1
One Range, Two Colors 4
Error 1004 - Unable to set invertifnegative 7

Top