On Tue, 3 Jun 2008 10:37:03 -0700, ExcelMonkey
wrote:

I need a quick way to extract the sheet name from this external address"

'[ABC.xls]Sheet1'!$C$2

A quick way would be to use a regex pattern which takes out everything
between the "]" and "!". Does anyone know what this pattern would look like?

Any ideas?

Thanks

EM

Something like:

".*?]([^'!]*).*"

will capture the sheet name into group 1, so can be used with the replace
method.

I would define the Sheet name, for this example, as everything between the "]"
and the single quote. If you also want to capture the single quote, then
change the "'" to a "!".

e.g.:

Dim re as object
Set re = createobject("vbscript.regexp")
re.Global = True
re.Pattern = ".*?\]([^'!]*).*"
'Result = re.Replace(str, "$1")
--ron