Links

[Khalil Handal]

Hi,
I have few workbooks in the same folder.
One of the books is "statistics" with only one sheet "sheet1". I gather
information from all the other workbooks in that folder.
If I copy all the workbooks to another drive and another folder,
"statistics" workbook will not be able to find the values.
Ex.: In cell D4 I have
='C:\Stony\[statistics.xls]Marks'!$K$67

I moved the files to Drive D:\samples
Here the problem happens.

I am looking for a VBA code to do this copying where it tells Excell to
"look in the current folder"
Doing this will not affect copying all the files to a different folder or
drive

Any help is appriciated.

 

[OssieMac]

Hi Khalil,

There may be better ways of doing this but what I have done in the past is
to open both workbooks in their original folders and then use Save As to save
one or both workbooks in alternative folders. Close the workbooks and if
prompted to save changes again then do so because I think that is when the
links are all updated. Excel simply handles the new paths for you.

To be on the safe side leave the original workbooks in the old folder until
you are satisfied that everything is working as it should with the new
workbooks. You can then delete the old copies.

Regards,

OssieMac

 

[Khalil Handal]

Hi,
Thanks for the note. I didn't know this even t seems a simple solution.
My interest n VBA code because it looks more easy specially for large number
of files.

Hi Khalil,

There may be better ways of doing this but what I have done in the past is
to open both workbooks in their original folders and then use Save As to
save
one or both workbooks in alternative folders. Close the workbooks and if
prompted to save changes again then do so because I think that is when the
links are all updated. Excel simply handles the new paths for you.

To be on the safe side leave the original workbooks in the old folder
until
you are satisfied that everything is working as it should with the new
workbooks. You can then delete the old copies.

Regards,

OssieMac

Hi,
I have few workbooks in the same folder.
One of the books is "statistics" with only one sheet "sheet1". I gather
information from all the other workbooks in that folder.
If I copy all the workbooks to another drive and another folder,
"statistics" workbook will not be able to find the values.
Ex.: In cell D4 I have
='C:\Stony\[statistics.xls]Marks'!$K$67

I moved the files to Drive D:\samples
Here the problem happens.

I am looking for a VBA code to do this copying where it tells Excell to
"look in the current folder"
Doing this will not affect copying all the files to a different folder or
drive

Any help is appriciated.