Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 66
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 417
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 66
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 66
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 417
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 417
Default Find source WS from Chart

Great tip Jon! Thanks.

--
Regards,
Bill Renaud



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
How to find/edit combo box source? Gdareos[_2_] New Users to Excel 3 December 20th 08 12:31 PM
pie chart & column chart w/same source data martymi Charts and Charting in Excel 2 May 1st 07 04:25 PM
How do you link chart source data when you copy the chart? mamagirl Charts and Charting in Excel 1 December 8th 06 02:40 AM
How to find a query source TonyL Excel Worksheet Functions 1 March 5th 06 06:24 AM
find out line count source in vbe tom cang Excel Programming 0 November 26th 04 04:31 AM


All times are GMT +1. The time now is 04:07 AM.

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"