Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
This is a start:
"].*'" But it extracts the "]" as well. Need to find a way to exclude the "]" in the result. Thanks EM "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=Tr
Actually to clarify
"].*'" Matches both the "]" and the single quote. I want neither. Thanks EM "ExcelMonkey" wrote: This is a start: "].*'" But it extracts the "]" as well. Need to find a way to exclude the "]" in the result. Thanks EM "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
You can do what you want without using Regular Expressions...
ExternalAddress = "'[ABC.xls]Sheet1'!$C$2" SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0) Rick "ExcelMonkey" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=Tr
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 "Rick Rothstein (MVP - VB)" wrote: You can do what you want without using Regular Expressions... ExternalAddress = "'[ABC.xls]Sheet1'!$C$2" SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0) Rick "ExcelMonkey" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=Tr
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 "ExcelMonkey" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: You can do what you want without using Regular Expressions... ExternalAddress = "'[ABC.xls]Sheet1'!$C$2" SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0) Rick "ExcelMonkey" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=Tr
Thanks for the detail. Appreciate it.
EM "Rick Rothstein (MVP - VB)" wrote: 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 "ExcelMonkey" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: You can do what you want without using Regular Expressions... ExternalAddress = "'[ABC.xls]Sheet1'!$C$2" SheetName = Split(Split(ExternalAddress, "]")(1), "'")(0) Rick "ExcelMonkey" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regex Pattern to extract Sheet Name from .Address(external:=True)
On Tue, 03 Jun 2008 19:01:10 -0400, Ron Rosenfeld
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does Address(external:=True) mean? | Excel Programming | |||
Another Problem with Regex Pattern | Excel Programming | |||
Help with a regex pattern please | Excel Programming | |||
Help with a Regex Pattern | Excel Programming | |||
When do I need Rng.Address(External:=True) | Excel Programming |