Regex Pattern to extract Sheet Name from .Address(external:=True)

[ExcelMonkey]

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

 

[ExcelMonkey]

This is a start:

"].*'"

But it extracts the "]" as well. Need to find a way to exclude the "]" in
the result.

Thanks

EM

 

[ExcelMonkey]

Actually to clarify

"].*'"

Matches both the "]" and the single quote. I want neither.

Thanks

EM

This is a start:

"].*'"

But it extracts the "]" as well. Need to find a way to exclude the "]" in
the result.

Thanks

EM

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

 

[Rick Rothstein \(MVP - VB\)]

You can do what you want without using Regular Expressions...

ExternalAddress = "'[ABC.xls]Sheet1'!$C$2"
SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0)

Rick

 

[Peter T]

Think I recall from a recent thread of yours you get that address with
rng.Address(external:=true)

If that's what you are doing simply
sheetName = rng.Parent.Name

Otherwise, yes of course parse the string between the ] & ! but also remove
any apostrophes that might be included that embrace the name.

Regards,
Peter T

 

[ExcelMonkey]

What is the role of the (1) and the (0)? I understand that Split will spit
the string using the delimiter and pass to an array variable. Do the numbers
in brackets denote the element of the 1D array that you wish to return the
value of?

Thanks

EM

You can do what you want without using Regular Expressions...

ExternalAddress = "'[ABC.xls]Sheet1'!$C$2"
SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0)

Rick

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

 

[Rick Rothstein \(MVP - VB\)]

Split creates the array in memory... you do not have to assign it to an
array variable to access elements from it. In memory, Split(Text,Delimiter)
creates a temporary array (normally awaiting assignment to an array
variable) AND it is a true array so it has elements. Just like if MyArray is
an array, MyArray(1) references the 2nd element (in a zero-based) array,
Split(Text,Delimiter)(1) references the 2nd element of the array created by
the Split function in memory (by the way, all arrays returned from Split are
always zero-based, no matter what the Option Base setting is). All my
statement is doing is grabbing the 2nd element produced by Split'ting your
ExternalAddress using "]" as the delimiter (since there will always be only
one closing square bracket, the sheet name will always appear in the 2nd
array element produced by the Split function), which is a String value, and
feeding it into another Split function call, using the apostrophe as the
delimiter, and that the sheet name part will always be in the 1st element
produced by that Split function call.

Rick

What is the role of the (1) and the (0)? I understand that Split will
spit
the string using the delimiter and pass to an array variable. Do the
numbers
in brackets denote the element of the 1D array that you wish to return the
value of?

Thanks

EM

You can do what you want without using Regular Expressions...

ExternalAddress = "'[ABC.xls]Sheet1'!$C$2"
SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0)

Rick

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

 

[ExcelMonkey]

Thanks for the detail. Appreciate it.

EM

Split creates the array in memory... you do not have to assign it to an
array variable to access elements from it. In memory, Split(Text,Delimiter)
creates a temporary array (normally awaiting assignment to an array
variable) AND it is a true array so it has elements. Just like if MyArray is
an array, MyArray(1) references the 2nd element (in a zero-based) array,
Split(Text,Delimiter)(1) references the 2nd element of the array created by
the Split function in memory (by the way, all arrays returned from Split are
always zero-based, no matter what the Option Base setting is). All my
statement is doing is grabbing the 2nd element produced by Split'ting your
ExternalAddress using "]" as the delimiter (since there will always be only
one closing square bracket, the sheet name will always appear in the 2nd
array element produced by the Split function), which is a String value, and
feeding it into another Split function call, using the apostrophe as the
delimiter, and that the sheet name part will always be in the 1st element
produced by that Split function call.

Rick

What is the role of the (1) and the (0)? I understand that Split will
spit
the string using the delimiter and pass to an array variable. Do the
numbers
in brackets denote the element of the 1D array that you wish to return the
value of?

Thanks

EM

You can do what you want without using Regular Expressions...

ExternalAddress = "'[ABC.xls]Sheet1'!$C$2"
SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0)

Rick


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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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 "!".

That should read " delete the "'" "
--ron