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