Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
I have a case where I want to branch code when a user clicks on a Toolbar
button when a chart is the active sheet. I want to find out where the source data from the chart is coming from. My initial thought was to use .XValues, and branch my code based on the location of the source data. When I have the Chart Active, and go to the Chart Menu and select Source Data, I get a dialog where I can clearly see the chart knows exactly where the data is coming from. If I turn on recording, and manipulate that window, nothing gets recorded. However, in VBA, when I grab a series, and look as .Values or .XValues, it returns a variant array. So far, the only way I have been able to determine the worksheet the .XValues came from is to get Series.Formula, and actually parse the name out of the string. Is there a better way of determining where the source data from a chart (or series really) is coming from? Thanks, Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
<<Is there a better way of determining where the source data from a chart
(or series really) is coming from? In general, I don't think so. Remember that a chart can have data from multiple worksheets, even if it is not a combination or mixed data chart with a secondary axis. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Bill,
Thanks for the response. I know that in general the data sets can come from anywhere, but in this particular application I know all the source data is coming from one worksheet. I was hoping something like ..Series(1).XValues.RefersToRange.Parent or something like that would just give me the Worksheet object I was looking for. Bob "Bill Renaud" wrote: <<Is there a better way of determining where the source data from a chart (or series really) is coming from? In general, I don't think so. Remember that a chart can have data from multiple worksheets, even if it is not a combination or mixed data chart with a secondary axis. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Indeed only way is to parse the formula, eg
Sub test() Dim s As String s = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Formula s = Split(s, ",")(2) s = Left$(s, InStr(2, s, "!") - 1) s = Replace(s, "'", "") MsgBox s 'Debug.Print s End Sub Regards, Peter T "INTP56" wrote in message ... Bill, Thanks for the response. I know that in general the data sets can come from anywhere, but in this particular application I know all the source data is coming from one worksheet. I was hoping something like .Series(1).XValues.RefersToRange.Parent or something like that would just give me the Worksheet object I was looking for. Bob "Bill Renaud" wrote: <<Is there a better way of determining where the source data from a chart (or series really) is coming from? In general, I don't think so. Remember that a chart can have data from multiple worksheets, even if it is not a combination or mixed data chart with a secondary axis. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Peter,
I'm not familiar with the split command, I'll have to look into it. Bob P.S. Here is what I did: Public Sub ToolBarButtonCode() 'This code is being run because of an .OnAction property of a toolbar button. Dim wbActive As Workbook, wsTest As Worksheet, chtTest As Chart Dim strChartFormula As String, lngStart As Long, lngLength As Long On Error Resume Next Set wbActive = ActiveWorkbook Set wsTest = ActiveSheet Set chtTest = ActiveSheet On Error GoTo 0 If wbActive Is Nothing Or (wsTest Is Nothing And chtTest Is Nothing) Then MsgBox "Action not available" Exit Sub End If If Not chtTest Is Nothing Then strChartFormula = chtTest.SeriesCollection(1).Formula lngStart = 1 + InStr(1, strChartFormula, "'") lngLength = InStr(1, strChartFormula, "!") - lngStart - 1 On Error Resume Next Set wsTest = wbActive.Worksheets(Mid(strChartFormula, lngStart, lngLength)) On Error GoTo 0 End If If wsTest Is Nothing Then MsgBox "Chart data not in active workbook" Exit Sub End If 'At this point, wsTest should hold the worksheet object of the source data. MsgBox wsTest.Name End Sub "Peter T" wrote: Indeed only way is to parse the formula, eg Sub test() Dim s As String s = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Formula s = Split(s, ",")(2) s = Left$(s, InStr(2, s, "!") - 1) s = Replace(s, "'", "") MsgBox s 'Debug.Print s End Sub Regards, Peter T "INTP56" wrote in message ... Bill, Thanks for the response. I know that in general the data sets can come from anywhere, but in this particular application I know all the source data is coming from one worksheet. I was hoping something like .Series(1).XValues.RefersToRange.Parent or something like that would just give me the Worksheet object I was looking for. Bob "Bill Renaud" wrote: <<Is there a better way of determining where the source data from a chart (or series really) is coming from? In general, I don't think so. Remember that a chart can have data from multiple worksheets, even if it is not a combination or mixed data chart with a secondary axis. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Locating the worksheet name can be very problematic. In your code:
lngStart = 1 + InStr(1, strChartFormula, "'") lngLength = InStr(1, strChartFormula, "!") - lngStart - 1 ....you obtain the length of the string to extract (lngLength) by starting at character 1 again. This could cause an error, if the worksheet name does not have any spaces in it, and therefore, there will be no single quotes in the string. You might try: lngStart = 1 + InStr(1, strChartFormula, "(") lngLength = InStr(lngStart, strChartFormula, "!") - 1 .... to insure that the length of the string to extract at least starts at the position of lngStart, otherwise lngLength could end up being a negative number. This assumes that you are using the label argument in the SERIES formula. If not, then you might have to search for a comma to find the beginning of the X data. After extracting the worksheet name, if single quotes surround the extracted string, delete them. For worksheets that have no spaces in the name, the SERIES formula will appear as: =SERIES(SheetName!$B$1,SheetName!$A$2:$A$100,Sheet Name!$B$2:$B$100,1) Note that there are no single quotes in the above example. If the following example, if the series has no label, then the first argument will be missing: =SERIES(,SheetName!$A$2:$A$100,SheetName!$B$2:$B$1 00,1) This also assumes that you are not using named ranges in the formulas to create a dynamic chart. If so, then the above series might appear as: =SERIES(,'WorkbookName.xls'!XNamedRange,'WorkbookN ame.xls'!YNamedRange,1) ...where the string inside the single quotes would be the WORKBOOK (file) name, not the worksheet name. You would then have to fetch XNamedRange from the list of names, then parse it's 'RefersTo' property to get the worksheet name. Hope this helps (HTH)! -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Rather than spend many hours writing and rewriting code to parse the series
formula, use this class module that John Walkenbach has developed: http://www.j-walk.com/ss/excel/tips/tip83.htm - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "INTP56" wrote in message ... I have a case where I want to branch code when a user clicks on a Toolbar button when a chart is the active sheet. I want to find out where the source data from the chart is coming from. My initial thought was to use .XValues, and branch my code based on the location of the source data. When I have the Chart Active, and go to the Chart Menu and select Source Data, I get a dialog where I can clearly see the chart knows exactly where the data is coming from. If I turn on recording, and manipulate that window, nothing gets recorded. However, in VBA, when I grab a series, and look as .Values or .XValues, it returns a variant array. So far, the only way I have been able to determine the worksheet the .XValues came from is to get Series.Formula, and actually parse the name out of the string. Is there a better way of determining where the source data from a chart (or series really) is coming from? Thanks, Bob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find source WS from Chart
Great tip Jon! Thanks.
-- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find/edit combo box source? | New Users to Excel | |||
pie chart & column chart w/same source data | Charts and Charting in Excel | |||
How do you link chart source data when you copy the chart? | Charts and Charting in Excel | |||
How to find a query source | Excel Worksheet Functions | |||
find out line count source in vbe | Excel Programming |