path

[Khalil Handal]

Hi to all,
I have a workbook called(test) with one single sheet (sheet1). This sheet
(sheet1) is a summary sheet taken from 24 different books that has the
names: g1a, g1b, g2a, g2b, .... , g24b. All of the workbooks has the same
number of sheets and sheet names; I am using values form 2 sheets (Persoanl
and constant) to be copied from each workbook.

In the cells I see something like this:
='D:\newfolder\sample\[G1a.xls]Personal'!K60
='D:\newfolder\sample\[G1b.xls]constants'!b7
.....
.....

The problem is that when I copy all the workbooks to another folder I will
have a link problem to find the values.

Is there a way to do this using VBA code so as the code will be like: "use
the file in the same path (current folder??) of this active workbook (test)?
in order to get the values.

Note: in the workbook test I have around 150 values linked in the range
B10:g26

 

[Dave Peterson]

If you look at VBA's help for .linksources, you'll see a way to loop through the
the links.

Then your code can look for filenames (g1a.xls through g24b.xls) in each of
those links. If found, then change the link to point to the new location.

If you don't have any other links in the workbook, you wouldn't even have to
look for the filenames. Just change each of the links.

VBA's help has an example of that looping technique.

Hi to all,
I have a workbook called(test) with one single sheet (sheet1). This sheet
(sheet1) is a summary sheet taken from 24 different books that has the
names: g1a, g1b, g2a, g2b, .... , g24b. All of the workbooks has the same
number of sheets and sheet names; I am using values form 2 sheets (Persoanl
and constant) to be copied from each workbook.

In the cells I see something like this:
='D:\newfolder\sample\[G1a.xls]Personal'!K60
='D:\newfolder\sample\[G1b.xls]constants'!b7
....
....

The problem is that when I copy all the workbooks to another folder I will
have a link problem to find the values.

Is there a way to do this using VBA code so as the code will be like: "use
the file in the same path (current folder??) of this active workbook (test)?
in order to get the values.

Note: in the workbook test I have around 150 values linked in the range
B10:g26

 

[Khalil Handal]

Hi,
I looked for the VBA help and found this code in Link Sources Method:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

I don't know that much about VBA;
I posted the code in the "this workbook" and don't know how it will work!!
Should it be in a module??? or as Macro??

I think I am not finding out how to do it?

If you look at VBA's help for .linksources, you'll see a way to loop
through the
the links.

Then your code can look for filenames (g1a.xls through g24b.xls) in each
of
those links. If found, then change the link to point to the new location.

If you don't have any other links in the workbook, you wouldn't even have
to
look for the filenames. Just change each of the links.

VBA's help has an example of that looping technique.

Hi to all,
I have a workbook called(test) with one single sheet (sheet1). This sheet
(sheet1) is a summary sheet taken from 24 different books that has the
names: g1a, g1b, g2a, g2b, .... , g24b. All of the workbooks has the same
number of sheets and sheet names; I am using values form 2 sheets
(Persoanl
and constant) to be copied from each workbook.

In the cells I see something like this:
='D:\newfolder\sample\[G1a.xls]Personal'!K60
='D:\newfolder\sample\[G1b.xls]constants'!b7
....
....

The problem is that when I copy all the workbooks to another folder I
will
have a link problem to find the values.

Is there a way to do this using VBA code so as the code will be like:
"use
the file in the same path (current folder??) of this active workbook
(test)?
in order to get the values.

Note: in the workbook test I have around 150 values linked in the range
B10:g26

 

[Dave Peterson]

If you don't have any other links to worry about, maybe just recording a macro
when you change one link (and repeat 23 (or 47) more times) would be sufficient:

ActiveWorkbook.ChangeLink _
Name:="C:\My Documents\Excel\book1.xls", _
NewName:="C:\My Documents\Excel\book999.xls", _
Type:=xlExcelLinks

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi,
I looked for the VBA help and found this code in Link Sources Method:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

I don't know that much about VBA;
I posted the code in the "this workbook" and don't know how it will work!!
Should it be in a module??? or as Macro??

I think I am not finding out how to do it?

If you look at VBA's help for .linksources, you'll see a way to loop
through the
the links.

Then your code can look for filenames (g1a.xls through g24b.xls) in each
of
those links. If found, then change the link to point to the new location.

If you don't have any other links in the workbook, you wouldn't even have
to
look for the filenames. Just change each of the links.

VBA's help has an example of that looping technique.

Hi to all,
I have a workbook called(test) with one single sheet (sheet1). This sheet
(sheet1) is a summary sheet taken from 24 different books that has the
names: g1a, g1b, g2a, g2b, .... , g24b. All of the workbooks has the same
number of sheets and sheet names; I am using values form 2 sheets
(Persoanl
and constant) to be copied from each workbook.

In the cells I see something like this:
='D:\newfolder\sample\[G1a.xls]Personal'!K60
='D:\newfolder\sample\[G1b.xls]constants'!b7
....
....

The problem is that when I copy all the workbooks to another folder I
will
have a link problem to find the values.

Is there a way to do this using VBA code so as the code will be like:
"use
the file in the same path (current folder??) of this active workbook
(test)?
in order to get the values.

Note: in the workbook test I have around 150 values linked in the range
B10:g26

 

[Khalil Handal]

Thanks Dave,
I will try this option and see if i will be able to succeed!!!

If you don't have any other links to worry about, maybe just recording a
macro
when you change one link (and repeat 23 (or 47) more times) would be
sufficient:

ActiveWorkbook.ChangeLink _
Name:="C:\My Documents\Excel\book1.xls", _
NewName:="C:\My Documents\Excel\book999.xls", _
Type:=xlExcelLinks

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi,
I looked for the VBA help and found this code in Link Sources Method:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

I don't know that much about VBA;
I posted the code in the "this workbook" and don't know how it will
work!!
Should it be in a module??? or as Macro??

I think I am not finding out how to do it?

If you look at VBA's help for .linksources, you'll see a way to loop
through the
the links.

Then your code can look for filenames (g1a.xls through g24b.xls) in
each
of
those links. If found, then change the link to point to the new
location.

If you don't have any other links in the workbook, you wouldn't even
have
to
look for the filenames. Just change each of the links.

VBA's help has an example of that looping technique.

Khalil Handal wrote:

Hi to all,
I have a workbook called(test) with one single sheet (sheet1). This
sheet
(sheet1) is a summary sheet taken from 24 different books that has the
names: g1a, g1b, g2a, g2b, .... , g24b. All of the workbooks has the
same
number of sheets and sheet names; I am using values form 2 sheets
(Persoanl
and constant) to be copied from each workbook.

In the cells I see something like this:
='D:\newfolder\sample\[G1a.xls]Personal'!K60
='D:\newfolder\sample\[G1b.xls]constants'!b7
....
....

The problem is that when I copy all the workbooks to another folder I
will
have a link problem to find the values.

Is there a way to do this using VBA code so as the code will be like:
"use
the file in the same path (current folder??) of this active workbook
(test)?
in order to get the values.

Note: in the workbook test I have around 150 values linked in the
range
B10:g26