L
Legacy 98055
Guest
I have to determine several references based upon a link address that my code will not know ahead of time.
For example:
"Sheet1!J:J"
This is Sheet1 of ThisWorkbook, column J
"[Book1.xls]Sheet2!A:A"
This is Sheet2 of Book1.xls, column A
Set SomeWorkbook = Evaluate("=INDIRECT(""Sheet1!A:A"")").Parent.Parent
I was hoping that this would evaluate to a range, the parent worksheet of the range, and the parent workbook of the worksheet.
By the way, I am not lifting this out of some formula that has been entered into a cell. This string is in a cell formatted as text.
I know I could parse the string and determine if the reference is remote or even use the LinkSources method, but I was hoping to use the simpler syntax if possible.
Thanks for any help!
For example:
"Sheet1!J:J"
This is Sheet1 of ThisWorkbook, column J
"[Book1.xls]Sheet2!A:A"
This is Sheet2 of Book1.xls, column A
Set SomeWorkbook = Evaluate("=INDIRECT(""Sheet1!A:A"")").Parent.Parent
I was hoping that this would evaluate to a range, the parent worksheet of the range, and the parent workbook of the worksheet.
By the way, I am not lifting this out of some formula that has been entered into a cell. This string is in a cell formatted as text.
I know I could parse the string and determine if the reference is remote or even use the LinkSources method, but I was hoping to use the simpler syntax if possible.
Thanks for any help!