Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
If I have a list of worksheets in a workbook, with VBA, how do I determine
the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#2
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
I'm wondering if the charting group can assist.
"Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
Hi Barb,
Sub test() Dim nCnt As Long Dim chtObj As ChartObject Dim sr As Series Dim vY, vX Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets nCnt = 0 For Each chtObj In ActiveSheet.ChartObjects nCnt = nCnt + 1 With chtObj.Chart MsgBox "Chart-Name : " & .Name & vbCr & _ "No of Series : " & .SeriesCollection.Count, , ws.Name For Each sr In .SeriesCollection If IsArray(vY) Then Erase vY If IsArray(vX) Then Erase vX vY = sr.Values vX = sr.XValues ''what do you want to do with these ? Next End With Next If nCnt = 0 Then MsgBox "No Charts in " & ws.Name End If Next End Sub I don't know what you mean by - And how do I display the SERIES #, the XVAL and the YVAL for each series? but maybe you can adapt the above to do what you want. Regards, Peter T "Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#4
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
All you have to do is add the code to write out the information of
interest to you. Option Explicit Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _ ByRef ChartSeriesData As Worksheet) 'Should actually check if these sheets exist Set WKSChart = aWB.Worksheets.Add Set ChartSeriesData = aWB.Worksheets.Add WKSChart.Name = "WKS Charts" ChartSeriesData.Name = "Chart Series" 'also need to add headers End Sub Sub writeChartInfo(ByRef TargCell As Range, _ aChart As Chart) 'write data of interest in row of targcell, then Set TargCell = TargCell.Offset(1, 0) End Sub Sub writeSeriesInfo(ByRef TargCell As Range, _ aChart As Chart) Dim aSeries As Series For Each aSeries In aChart.SeriesCollection 'write chart series info to TargCell row, then Set TargCell = TargCell.Offset(1, 0) Next aSeries End Sub Sub analyzeAllEmbeddedCharts() Dim aWS As Worksheet, aChartObj As ChartObject, _ WKSChart As Worksheet, ChartSeriesData As Worksheet, _ ChartWKSCell As Range, SeriesWKSCell As Range InitSetup ActiveWorkbook, WKSChart, ChartSeriesData Set ChartWKSCell = WKSChart.Cells(2, 1) Set SeriesWKSCell = ChartSeriesData.Cells(2, 1) For Each aWS In ActiveWorkbook.Worksheets For Each aChartObj In aWS.ChartObjects writeChartInfo ChartWKSCell, aChartObj.Chart writeSeriesInfo SeriesWKSCell, aChartObj.Chart Next aChartObj Next aWS End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I'm wondering if the charting group can assist. "Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#5
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
Tushar,
That gets me a lot closer. What I want to know is programmatically, how do I identify the X and Y axis entries for the chart and series of interest and print them out. What I want is WORKSHEET NAME CHART NAME SERIES # X VALUES Y VALUES on a spreadsheet. How do I find out how to do this? Thanks, Barb Reinhardt "Tushar Mehta" wrote: All you have to do is add the code to write out the information of interest to you. Option Explicit Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _ ByRef ChartSeriesData As Worksheet) 'Should actually check if these sheets exist Set WKSChart = aWB.Worksheets.Add Set ChartSeriesData = aWB.Worksheets.Add WKSChart.Name = "WKS Charts" ChartSeriesData.Name = "Chart Series" 'also need to add headers End Sub Sub writeChartInfo(ByRef TargCell As Range, _ aChart As Chart) 'write data of interest in row of targcell, then Set TargCell = TargCell.Offset(1, 0) End Sub Sub writeSeriesInfo(ByRef TargCell As Range, _ aChart As Chart) Dim aSeries As Series For Each aSeries In aChart.SeriesCollection 'write chart series info to TargCell row, then Set TargCell = TargCell.Offset(1, 0) Next aSeries End Sub Sub analyzeAllEmbeddedCharts() Dim aWS As Worksheet, aChartObj As ChartObject, _ WKSChart As Worksheet, ChartSeriesData As Worksheet, _ ChartWKSCell As Range, SeriesWKSCell As Range InitSetup ActiveWorkbook, WKSChart, ChartSeriesData Set ChartWKSCell = WKSChart.Cells(2, 1) Set SeriesWKSCell = ChartSeriesData.Cells(2, 1) For Each aWS In ActiveWorkbook.Worksheets For Each aChartObj In aWS.ChartObjects writeChartInfo ChartWKSCell, aChartObj.Chart writeSeriesInfo SeriesWKSCell, aChartObj.Chart Next aChartObj Next aWS End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I'm wondering if the charting group can assist. "Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
I added the Worksheets loop at the last moment and forgot to change -
For Each chtObj In ActiveSheet.ChartObjects to For Each chtObj In ws.ChartObjects You added a little more about your requirements in your reply to Tushar, but what do you mean by "SERIES #", the actual names of each series perhaps. If so include something like this when looping the SeriesCollection serName = sr.Name With the combination of routines suggested to you I think there's enough for you to piece something together to suit your requirements. Though you would need to carefully keep track of where you are writing details to cells on the worksheet, not only with each chart but additionally when writing multiple sets of series values, assuming that's what you want to do. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Hi Barb, Sub test() Dim nCnt As Long Dim chtObj As ChartObject Dim sr As Series Dim vY, vX Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets nCnt = 0 For Each chtObj In ActiveSheet.ChartObjects nCnt = nCnt + 1 With chtObj.Chart MsgBox "Chart-Name : " & .Name & vbCr & _ "No of Series : " & .SeriesCollection.Count, , ws.Name For Each sr In .SeriesCollection If IsArray(vY) Then Erase vY If IsArray(vX) Then Erase vX vY = sr.Values vX = sr.XValues ''what do you want to do with these ? Next End With Next If nCnt = 0 Then MsgBox "No Charts in " & ws.Name End If Next End Sub I don't know what you mean by - And how do I display the SERIES #, the XVAL and the YVAL for each series? but maybe you can adapt the above to do what you want. Regards, Peter T "Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
#8
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Chart VBA Question
I don't think XLM is any better than John's class module. John's approach is
a bit tricky to understand at first, but it's very useful. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Tushar Mehta" wrote in message om... Hi Barb, I don't know how much VBA you know. Consequently, I don't know how much detailed help you need. To get the X-values themselves use the XValues property of the series. The Y-values are available through the Values property. However, this will yield the actual numeric values themselves. If you want to know what range/name the series contains you will have to use something like John Walkenbach's http://www.j-walk.com/ss/excel/tips/tip83.htm There may be a way to get the information more directly witha XLM macro but I don't know how. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar, That gets me a lot closer. What I want to know is programmatically, how do I identify the X and Y axis entries for the chart and series of interest and print them out. What I want is WORKSHEET NAME CHART NAME SERIES # X VALUES Y VALUES on a spreadsheet. How do I find out how to do this? Thanks, Barb Reinhardt "Tushar Mehta" wrote: All you have to do is add the code to write out the information of interest to you. Option Explicit Sub InitSetup(aWB As Workbook, ByRef WKSChart As Worksheet, _ ByRef ChartSeriesData As Worksheet) 'Should actually check if these sheets exist Set WKSChart = aWB.Worksheets.Add Set ChartSeriesData = aWB.Worksheets.Add WKSChart.Name = "WKS Charts" ChartSeriesData.Name = "Chart Series" 'also need to add headers End Sub Sub writeChartInfo(ByRef TargCell As Range, _ aChart As Chart) 'write data of interest in row of targcell, then Set TargCell = TargCell.Offset(1, 0) End Sub Sub writeSeriesInfo(ByRef TargCell As Range, _ aChart As Chart) Dim aSeries As Series For Each aSeries In aChart.SeriesCollection 'write chart series info to TargCell row, then Set TargCell = TargCell.Offset(1, 0) Next aSeries End Sub Sub analyzeAllEmbeddedCharts() Dim aWS As Worksheet, aChartObj As ChartObject, _ WKSChart As Worksheet, ChartSeriesData As Worksheet, _ ChartWKSCell As Range, SeriesWKSCell As Range InitSetup ActiveWorkbook, WKSChart, ChartSeriesData Set ChartWKSCell = WKSChart.Cells(2, 1) Set SeriesWKSCell = ChartSeriesData.Cells(2, 1) For Each aWS In ActiveWorkbook.Worksheets For Each aChartObj In aWS.ChartObjects writeChartInfo ChartWKSCell, aChartObj.Chart writeSeriesInfo SeriesWKSCell, aChartObj.Chart Next aChartObj Next aWS End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I'm wondering if the charting group can assist. "Barb Reinhardt" wrote in message ... If I have a list of worksheets in a workbook, with VBA, how do I determine the following: 1) If there is a chart on the worksheet and the chart identification 2) If there is a chart ... how do I determine the number of series displayed in the chart And how do I display the SERIES #, the XVAL and the YVAL for each series? Thanks in advance, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Question | Charts and Charting in Excel | |||
Chart Question | Charts and Charting in Excel | |||
chart question | Excel Discussion (Misc queries) | |||
Chart question | Excel Discussion (Misc queries) | |||
chart question | Charts and Charting in Excel |