Linking to External Data that has been moved

[Excellion - ExcelForums.com]

Any help would be appreciated

I have this workbook that reads info in from data files (not exce
workbooks) which is then in turn used in another workbook t
calculate values. The problem is that these files were moved t
another location and now I either have to manually relink them all
or find a way to change where the excel workbook points when it trie
to refresh data

Before you say go to Edit > Links, that only works to restor
broken links between workbooks I need to know how to restore a lin
between where Excel is referencing the data files fo
import/refreshing. Right now the workbook keeps looking in the ol
location.

The structure is like this

Workbook1 links to Workbook2 links to raw data

Let's say for example, it was in C: but now the data and workbooks ar
in D: but the workbook still looks for the data in C:. It wouldn't b
a big deal but in Workbook2 there are several hundred references t
the raw data that all need to be relinked manually if I can't find
way to effect a global relink

Thanks in advance

 

[OJ]

Hi,
you could write a macro to change the QueryTable's connection
property...have all the files that you import moved from one place to
another same place or are they all now scatterred all over the place?
If it is literally C:\mydir\ to D:\mydir\ then something like this
would work..

Sub ChangeConx()
Dim intInc as Integer, intInc1 as Integer

For intInc = 1 to thisworkbook.sheets.count
With thisworkbbok.sheets(intInc)
For intInc1 = 1 to .querytables.count
With .querytables(intInc1)
.connection = "TEXT;D:\MyDir\" & YourNewFileName
End With
Next
End With
Next

End Sub

Untested but should point you in the right direction....

Hth,
O

 

[Arvi Laanemets]

Hi

Such problems are the reason I always design files which query external
data, to be connected to mapped network resource. P.e. you have some
database on \\OtherComputer\SharedFolder . You map this resource p.e. as
R: - now you create a query with data source as R:\YourSourceData. When the
database is moved to another share, or even to another computer/server, p.e.
to \\AnotherComputer\AnotherSharedFolder, then you remove the old mapping,
and map the new resource as R:. When you after that open your excel file,
the query is refreshed without problems.

 

[Excellion - ExcelForums.com]

Thanks for the help and advice

Just wondering, OJ, does that macro you wrote change all instances of
the new .Connection location? When I did a query to find the list of
it all I had around 300 of them.

Thanks again!

 

[Excellion - ExcelForums.com]

I also forgot to add, the files are all the same, just the location of
the files has changed. This is what the Connection info is

'TEXT;Z:\Trough\User1\Attrib\EBPs\Ships\Boats\boat1.lua

Does the marco replace the "Z:\Trough" part? Since the
"User1\Attrib\EBPs\Ships\Boats\boat1.lua" is correct. I just need it
to point to C:\blahbla\User1\Attrib\EBPs\Ships\Boats\boat1.lua

Thanks yet again

 

[Excellion - ExcelForums.com]

I've got it figured out now, this is the query to use to replace all
paths

Sub ChangeConx()
Dim intInc As Integer, intInc1 As Integer

For intInc = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(intInc)
For intInc1 = 1 To .QueryTables.Count
With .QueryTables(intInc1)
.Connection = Replace(.Connection, "BLAH, "BLAH2")

End With
Next
End With
Next

End Sub


Thanks everyone that helped

 

[Harlan Grove]

Arvi Laanemets wrote...

Such problems are the reason I always design files which query external
data, to be connected to mapped network resource. P.e. you have some
database on \\OtherComputer\SharedFolder . You map this resource p.e. as
R: - now you create a query with data source as R:\YourSourceData. When the
database is moved to another share, or even to another computer/server, p.e.
to \\AnotherComputer\AnotherSharedFolder, then you remove the old mapping,
and map the new resource as R:. When you after that open your excel file,
the query is refreshed without problems.

....

Indirection is the ideal way to do this sort of thing, but relying on
drive letter mapping has limited benefit. What do you do if there are
multiple data sources originally on different machines? They both can't
be mapped as R:. What happens if some but not all are moved to
different machines?

Better still is to use shortcuts, but you have to be sure to include
the .lnk extension at the end of the filename. At least for external
links to other workbooks, shortcuts work. For example, if the source
workbook were

X:\Y\Z\foobar.xls

and there were a shortcut to it stored as

C:\data\foobar.xls.lnk

the external reference

='C:\data\[foobar.xls.lnk]Sheet1'!X99

should return the same result as

='X:\Y\Z\[foobar.xls]Sheet1'!X99

I haven't confirmed that data fetched via Data > Import External Data
would work similarly with shortcuts, but it'd be something to try.

 

[Arvi Laanemets]

Hi

Arvi Laanemets wrote...
...

Indirection is the ideal way to do this sort of thing, but relying on
drive letter mapping has limited benefit. What do you do if there are
multiple data sources originally on different machines? They both can't
be mapped as R:. What happens if some but not all are moved to
different machines?

So long as they are used for different queries, you can map them as
different drives. I never have had a situation, where I needed more than 2
mappings (almost always the one mapping is enough, and I have only 2 shares
on server, which I use constantly) - I prefer all data sources used by
several users to be stored on server. So only situations like OP has, will
arise when source data are moved to another server, or to another share.

I myself use this technique to distribute Excel applications for LAN
clients: user maps the resource and sets it to be restored on open, opens
template application situated on server and saves it on his workstation's
hard disk. And it's all.


Arvi Laanemets

Better still is to use shortcuts, but you have to be sure to include
the .lnk extension at the end of the filename. At least for external
links to other workbooks, shortcuts work. For example, if the source
workbook were

X:\Y\Z\foobar.xls

and there were a shortcut to it stored as

C:\data\foobar.xls.lnk

the external reference

='C:\data\[foobar.xls.lnk]Sheet1'!X99

should return the same result as

='X:\Y\Z\[foobar.xls]Sheet1'!X99

I haven't confirmed that data fetched via Data > Import External Data
would work similarly with shortcuts, but it'd be something to try.