Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Debra Dalgleish: Pivot Table problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Debra Dalgleish: Pivot Table problem
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:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;Defa ultDir=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 Whe=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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default Debra Dalgleish: Pivot Table problem
You don't specify the chart's source data in the code, e.g.:

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

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

http://www.peltiertech.com/Excel/Cha...kChartVBA.html

Lee Hunter wrote:
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:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;Defa ultDir=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 Whe=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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Debra Dalgleish: Pivot Table problem
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" wrote:

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

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

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

http://www.peltiertech.com/Excel/Cha...kChartVBA.html

Lee Hunter wrote:
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:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;Defa ultDir=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 Whe=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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default Debra Dalgleish: Pivot Table problem
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 Hunter wrote:
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" wrote:


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

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

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

http://www.peltiertech.com/Excel/Cha...kChartVBA.html

Lee Hunter wrote:

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:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\1310\Quality\Metrics\Trip.mdb;D efaultDir=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 Whe=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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
using Debra Dalgleish toolbar code chrisnsmith Excel Discussion (Misc queries) 2 February 12th 09 05:30 PM
Re-sizing C. Boxes using Debra Dalgleish joesf16 Excel Discussion (Misc queries) 2 September 4th 07 07:58 AM
Debra Dalgleish Question VBA Noob Excel Worksheet Functions 7 November 9th 06 10:40 PM
Bless you, Debra Dalgleish & Pejo Sjoblom! [email protected] Excel Worksheet Functions 5 June 5th 06 02:46 PM
Debra Dalgleish nc Excel Discussion (Misc queries) 14 May 12th 06 12:41 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement