path

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil Handal - 16 Jul 2007 11:53 GMT

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

Reply to this Message

Dave Peterson - 16 Jul 2007 12:31 GMT

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
[quoted text clipped - 18 lines]
> Note: in the workbook test I have around 150 values linked in the range
> B10:g26

Signature

Dave Peterson

Reply to this Message

Khalil Handal - 16 Jul 2007 13:04 GMT

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
[quoted text clipped - 36 lines]
>> Note: in the workbook test I have around 150 values linked in the range
>> B10:g26

Reply to this Message

Dave Peterson - 16 Jul 2007 13:20 GMT

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:
[quoted text clipped - 56 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Khalil Handal - 16 Jul 2007 13:38 GMT

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
[quoted text clipped - 76 lines]
>> >
>> > Dave Peterson

Reply to this Message