Pivot Table and charting with VBA or Counting items

amy

I need to be able to create a chart with VBA based on data in sheet1.  In order to build a chart I need the DIFFERENT items in one column for the X Axis, the count of items for each of those for the data source and the labels for the Y axis.  So I turned to PivotTable.  So I can get a pivot table and build a chart from it BUT the code is fixed and that table may change.  So I need access to the components of the pivottable so I can select the lables, select the columns of data etc. so I can do this in code with variable.  It's either this or I need to duplicate some of the function of a pivot table like counting the items and accumulating or combining data for the chart from the source sheet.Thanks for looking.

And, you need a programmatic solution because...

Thanks Tushar,

I appreciate you responding.  Can you give me an idea of how to get the info from the pivot table?  

Is there a better way to approach the solution?  In previous programs I read data from the page as a recordset and I can easily manipulate that.  But I cannot figure out how to even create a recordset of the data on the worksheet, so that idea is out.

I have no idea how a recordset gets into this discussion, but have you considered a PivotChart?  That transfers to XL the responsiblity of adjusting the chart to reflect changes in the PT.

Originally Posted by ScottInTexasThanks Tushar,

I appreciate you responding.  Can you give me an idea of how to get the info from the pivot table?  

Is there a better way to approach the solution?  In previous programs I read data from the page as a recordset and I can easily manipulate that.  But I cannot figure out how to even create a recordset of the data on the worksheet, so that idea is out.
I have not been clear.  This need of a chart is a small part of a larger application.  

Users select a menu item from vb application I have developed.  This item exports a ton of data to a spreadsheet.  Now I have been asked to also create certain charts in the spreadsheet.  I MUST put the code for the charts IN the spread sheet VBA and not in my program.  I have a 19 col by 496 row sample of the data in this particular export.  I want to be able to create certain charts on a collection of this information.  There are 11 business a number of each in column B. There are certain events that have occured with each of these businesses.  What I can do is create a pivot table that lists the number of events for each of the businesses.  Then I can use the chart wizard to create the chart from the pivot table.Recordsets came into the conversation because I had said in previous applications I could open an excel sheet (with code) and view the data as a recordset.  This gives me complete control over each label and data value.  Since I am working WITHIN Excel I cannot get that kind of control, I don't know how.  Nothing more, just simple control of the data.  This was mentioned because you might have been able to provide a method for using recordsets.

Each time the data is exported,  the amount of data may change.  The pivot tables are built on the amount of data CURRENTLY on the sheet.  It doesn't know that this export has 212 rows and another has 495 rows or only 12 rows.  How is a pivot table, manually created while viewing a sheet, get called as a function and GROW according to changes in the AMOUNT OF data? Not just a change in a value, but even more values than the last time it was created.  Since the sheet is being CREATED from a template I can store the function and call it from my application.  But I will have to count valid rows, create a range and use the range to create the pivot table.  Now I have to chart from the pivot table.  But How do I know (Programatically) what row the pivot table labels are in.  What column do they start at, not the first since that is one of the fields, not the last because that is the Grand Total.  How many rows of data are in the pivot table?  One row on the sheet is occupied with the "Count Of" field.  One row is the totals of each column.

I hope this post provided more information that would lead you to understand what I needed.[/quote]

I still don't understand the problem.  My guess is that you are making it a lot more difficult that necessary.  I don't see the need for you to take apart a PT's contents.

Try this.  Turn on the macro recorder, create a PivotTable and a PivotChart, and turn off the recorder.  Customize the XL generated code as needed and put it wherever you want.  Now, when your code creates a PT, XL will give you the associated PivotChart.

Originally Posted by ScottInTexasI have not been clear.  This need of a chart is a small part of a larger application.  

{snip}
Thanks,  Here is the code from the macro

Code:   Charts.Add   ActiveChart.ChartType = xlColumnStacked   ActiveChart.SetSourceData Source:=Sheets("All Events").Range("G15")   ActiveChart.SeriesCollection.NewSeries   ActiveChart.SeriesCollection.NewSeries   ActiveChart.SeriesCollection.NewSeries   ActiveChart.SeriesCollection.NewSeries   ActiveChart.SeriesCollection(1).XValues = "='All Events'!R3C1:R7C1"   ActiveChart.SeriesCollection(1).Values = "='All Events'!R3C2:R7C2"   ActiveChart.SeriesCollection(1).Name = "='All Events'!R2C2"
Note the red line reference is row 3 to row 7.  In this case it is correct, but what about the next time this is run?  Maybe it is a range from 3 to 12.  It seems like I need to programatically find the first and last values in a column in a pivot table in order to do this with the same line Code:
ActiveChart.SeriesCollection(1).Values = "='All Events'!R" amp; intStartRow amp; "C" amp; inStartCol amp; ":R" amp; intLastRow amp; "C" amp; intLastCol
And you are probably right.  Making it more difficult is a fault I have.