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.
RocketDude wrote:
>
> 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
>
> --
> Matthew
--
Dave Peterson
|
Similar Threads
