Linking to External Data that has been moved

Any help would be appreciated

I have this workbook that reads info in from data files (not excel
workbooks) which is then in turn used in another workbook to
calculate values. The problem is that these files were moved to
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 tries
to refresh data.

Before you say go to Edit > Links, that only works to restore
broken links between workbooks I need to know how to restore a link
between where Excel is referencing the data files for
import/refreshing. Right now the workbook keeps looking in the old
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 are
in D: but the workbook still looks for the data in C:. It wouldn't be
a big deal but in Workbook2 there are several hundred references to
the raw data that all need to be relinked manually if I can't find a
way to effect a global relink.

Thanks in advance!

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

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.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Excellion - ExcelForums.com" <[email protected]> wrote in
message news:[email protected]...
> Any help would be appreciated
>
> I have this workbook that reads info in from data files (not excel
> workbooks) which is then in turn used in another workbook to
> calculate values. The problem is that these files were moved to
> 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 tries
> to refresh data.
>
> Before you say go to Edit > Links, that only works to restore
> broken links between workbooks I need to know how to restore a link
> between where Excel is referencing the data files for
> import/refreshing. Right now the workbook keeps looking in the old
> 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 are
> in D: but the workbook still looks for the data in C:. It wouldn't be
> a big deal but in Workbook2 there are several hundred references to
> the raw data that all need to be relinked manually if I can't find a
> way to effect a global relink.
>
> Thanks in advance!
>

Thanks for the help and advice :D

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!

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

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

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.

Hi


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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?


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.
>