A most ghetto-fabulous GetSourceData

[Taylor]

Excel has SetSourceData, which I love because it is so elegant.
Unfortunately, there was no yin to the yang: No such thing as
GetSourceData was made to accompany it.

I needed this long ago, but ended up writing some ridiculous
Chart.Series string parsery instead. Which was probably much more
reliable. But damn near indecipherable.

(And Jon Peltier has an excel Add-In to deal with this oversight, no?)

But sometimes there is something to simple, ugly, but devious code, so
I just thought, why not post something that breaks two well-
established rules of robust coding; No sendkeys and no clipboard:



Function GetSourceData(strChtName As String) as String
Dim clipSourceData As DataObject
Dim strSourceData As String

Set clipSourceData = New DataObject

Charts(strChtName).Activate

'Before you think I'm completely crazy, I superstitiously add the
'Numlock toggles as a harmless way to make sure the keys
'that matter (Ctrl+C) aren't sent before the dialog pops up
SendKeys "{NUMLOCK}{NUMLOCK}{NUMLOCK}{NUMLOCK}^c{ESC}"
Application.Dialogs(xlDialogChartSourceData).Show
clipSourceData.GetFromClipboard
strSourceData = clipSourceData.GetText

GetSourceData = strSourceData
MsgBox "Is ~this~ your card?" & vbCR & strSourceData

End Function

To boot, this returns nothing about whether it's plotted by rows or
columns, and if the Data Range is too complex to display, I'm fairly
certain your computer (and also possibly your head) will explode.*

When I whipped this up, I just had too big a smile on my face to worry
about such piddly consequences.

*Other side effects may include gnashing of teeth/wails of disbelief/
shock vomit. Please ask your doctor if GetSourceData is right for you.

 

[Peter T]

If interested I have an addin that can dump all series data to cells, and
optionally re-source the chart to the new cell source. Source data can be in
a different workbook or not in cells at all. The same addin can also
entirely remove all links in chart(s) to cells.

I'm afraid the source code is n/a although some of it's functions can be
accessed by VBA.

Regards,
Peter T

 

[Jon Peltier]

Pretty good. I'm sure the use of chicken's feet and strange chants improves
it's success rate. But who's superstitious.

I find that the vast majority of my charts have no clearly defined source
data range. Instead of something like this tool, I use John Walkenbach's
Chart Series Formula Class Module to extract ranges one series at a time.
See
http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/.

- Jon