Home Return the Worksheet that an Excel Chart is referencing using VBA
Reply: 0

Return the Worksheet that an Excel Chart is referencing using VBA

user1241 Published in July 18, 2018, 4:48 am

I need to be able to identify the worksheet that an excel chart (on a worksheet) is getting it's data from. I only need the data sheet which series 1 is referencing. I've started trying to extract the sheet name from .SeriesCollection(1).Formula but it gets realy complex. here's what I've got so far:

Sub GetChartDataSheet()

Dim DataSheetName As String
Dim DataSheet As Worksheet

DataSheetName = ActiveChart.SeriesCollection(1).Formula

DataSheetName = Left(DataSheetName, InStr(1, DataSheetName, "!$") - 1)
DataSheetName = WorksheetFunction.Replace(DataSheetName, 1, Len("=series("), "")
If Left(DataSheetName, 1) = "'" And Right(DataSheetName, 1) = "'" Then DataSheetName = Mid(DataSheetName, 2, Len(DataSheetName) - 2)
DataSheetName = Replace(DataSheetName, "''", "'")

Set DataSheet = Sheets(DataSheetName)    

End Sub

this works in a lot of cases, but if my users have a strange worksheet name (eg Sh'e e$,,t!3!$) it fails. the same goes if series 1 has been named (eg .SeriesCollection(1).Formula = "=SERIES(**"Hell,o !"**,'Sh''e e$,,t!3!$'!$B$2:$B$18,'Sh''e e$,,t!3!$'!$C$2:$C$18,1)".

Is there a simple way to solve this?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.319203 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO