Anyway to replace absolute file path in formula with relative or use Info(directory)?

[RocketDude]

Hi,

I have a workbook that looks summarizes data that is in multiple (60) other
workbooks, and want to know if there is someway to craft my look such that
the folder structure doesn't manner --I want to be able to share this
workbook with a teammate and not have the lookups fail, so long as the
folder structure at the file level is the same.

Here is the setup:

Summary file
C:\xxx\xxx\xxx\summary file.xls
Data files
C:\xxx\xxx\xxx\data files\folder1\data.xls
C:\xxx\xxx\xxx\data files\folder2\data.xls
....
C:\xxx\xxx\xxx\data files\folder60\data.xls

I want to somehow craft my lookup -- right now my lookup is
='C:\xxx\xxx\xxx\data files\folder1\[data.xls]worksheet'!$P3 -- so that the
C:\xxx\xxx\xxx\ is added automatically. I tried using INFO("directory") to
get the path, but I could figure out how to use that in the formula.

TIA

 

[Dave Peterson]

Maybe you could do something like this...

Instead of storing the link as a formula:
='C:\xxx\xxx\xxx\data files\folder1\[data.xls]worksheet'!$P3

Store it as text
$$$$$='%%%%\data files\folder1\[data.xls]worksheet'!$P3

Then you could have an auto_open macro that does a couple of mass changes:
edit|replace
what: %%%%
with: thisworkbook.path
replace all

And then the same kind of thing to change the strings to formulas:
edit|replace
what: $$$$$=
with: =
replace all

Something like this in code:

Option Explicit
Sub auto_open()

With Worksheets("sheet1")
.Cells.Replace what:="%%%%", replacement:=ThisWorkbook.Path, _
lookat:=xlPart, MatchCase:=False, searchorder:=xlByRows
.Cells.Replace what:="$$$$$=", replacement:="=", lookat:=xlPart, _
MatchCase:=False, searchorder:=xlByRows
End With

End Sub

Make sure those folders/files exist--it could mean trouble if the workbooks
couldn't be found--each formula would cause a dialog to popup looking for it.

Hi,

I have a workbook that looks summarizes data that is in multiple (60) other
workbooks, and want to know if there is someway to craft my look such that
the folder structure doesn't manner --I want to be able to share this
workbook with a teammate and not have the lookups fail, so long as the
folder structure at the file level is the same.

Here is the setup:

Summary file
C:\xxx\xxx\xxx\summary file.xls
Data files
C:\xxx\xxx\xxx\data files\folder1\data.xls
C:\xxx\xxx\xxx\data files\folder2\data.xls
...
C:\xxx\xxx\xxx\data files\folder60\data.xls

I want to somehow craft my lookup -- right now my lookup is
='C:\xxx\xxx\xxx\data files\folder1\[data.xls]worksheet'!$P3 -- so that the
C:\xxx\xxx\xxx\ is added automatically. I tried using INFO("directory") to
get the path, but I could figure out how to use that in the formula.

TIA