Links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil Handal - 23 Aug 2007 23:35 GMT

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.

Reply to this Message

OssieMac - 24 Aug 2007 04:48 GMT

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.
[quoted text clipped - 14 lines]
>
> Any help is appriciated.

Reply to this Message

Khalil Handal - 24 Aug 2007 07:29 GMT

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,
>
[quoted text clipped - 32 lines]
>>
>> Any help is appriciated.

Reply to this Message

Rate this thread: