Extracting the full path from a VLOOKUP formula

[shawnmandel]

How could I extract the full path from a VLOOKUP formula? For example all cells in the worksheet/workbook share the same path in their VLOOKUPs:

=VLOOKUP('C:\Documents and Settings\e0691\Desktop\AVS\Development\Costing Analysis v2.7\[Master Costing Analysis.xls]Analysis'!$B$24,'C:\Documents and Settings\e0691\Desktop\AVS\Development\Costing Analysis v2.7\[Master Costing Analysis.xls]Analysis'!$B$24:$K$500,1,FALSE)


How could I use VBA to extract "C:\Documents and Settings\e0691\Desktop\AVS\Development\Costing Analysis v2.7\Master Costing Analysis.xls" from the above VLOOKUP formula?


Any help would be greatly appreciated.


Thanks,
Shawn

 

[RobBroekhuis]

Depends on how variable your formulas are. If they are always in the format above, you could simply use:

s=MyCell.formula
MyXLS=mid(MyCell.formula,11,instr(MyCell.formula,"!&quot-12)



Rob

 

[shawnmandel]

Hi Rob,

The path and/or filename constantly changes, however, the overall format of the VLOOKUP will remain the same.

Thanks,
Shawn

 

[shawnmandel]

Here is the code that I have:

ExtractFile = Mid(FormulaText, 12, InStr(FormulaText, "]&quot - 12)

Do Until Left(nExtractFile, 1) = "["
iCount = iCount + 1
nExtractFile = Right(ExtractFile, iCount)
If iCount = Len(ExtractFile) Then Exit Do
Loop

I have a minor issue in that the nExtractFile includes the "[" character from the VLOOKUP. How would I modify the code in order to exclude the "[" from the nExtractFile variable? Once the "[" is excluded from nExtractFile, I would have the correct file name.

Thanks,
Shawn

 

[shawnmandel]

It's okay, I resolved my issue with the following extra line to drop the "[":

Right(nExtractFile, Len(nExtractFile) - 1)


Thanks,
Shawn