Thread: Problems with saving WorkSheets that have External Links

Does anyone know how I can resolve this issue ... I have a directory which
contains 129 worksheets which have links to external data (in a Master
Spreadsheet) -- I need to copy these files into a New Directory, but kee the
Master Spreadsheet (which they are linked to) in the original location. If
I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets
moved to the New Directory (where the file does not exist), but if I open
the worksheet (in the original directory/location) and Save As to the New
Directory, the worksheet saved in the New Directory maintains its link to
the Master Spreadsheet in the original directory/location. I hope I've
explained this clearly.

Here's my problem -- it's a bit time consuming to have to open each and
every worksheet and Save As to the New Location -- I'm not sure if a Batch
File (or Dos Command xcopy) would solve this -- Is there some code I could
use to Open each worksheet, Save As to the New directory, Close, then
perform this on each of the .xls files in the original directory? If so,
could you please point me in the direction with an example of the code.

Example:

Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget

Many Thanks in Advance.

Maybe the response in .excel????

doctorjones_md@yahoo.com wrote:
>
> Does anyone know how I can resolve this issue ... I have a directory which
> contains 129 worksheets which have links to external data (in a Master
> Spreadsheet) -- I need to copy these files into a New Directory, but kee the
> Master Spreadsheet (which they are linked to) in the original location. If
> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets
> moved to the New Directory (where the file does not exist), but if I open
> the worksheet (in the original directory/location) and Save As to the New
> Directory, the worksheet saved in the New Directory maintains its link to
> the Master Spreadsheet in the original directory/location. I hope I've
> explained this clearly.
>
> Here's my problem -- it's a bit time consuming to have to open each and
> every worksheet and Save As to the New Location -- I'm not sure if a Batch
> File (or Dos Command xcopy) would solve this -- Is there some code I could
> use to Open each worksheet, Save As to the New directory, Close, then
> perform this on each of the .xls files in the original directory? If so,
> could you please point me in the direction with an example of the code.
>
> Example:
>
> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
>
> Many Thanks in Advance.

--

Dave Peterson

Yes -- your idea in the Excel group did the trick brillantly (I wasn't
certain which group would be best suited for that particular question) -- I
apologize for the redundancy.

I did include one additional RFA on that question in the Excel group, so I
won't post it here. Thanks for all your help Dave.

Have a GREAT weekend!
====================
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44AE86CF.CB80B3E7@verizonXSPAM.net...
> Maybe the response in .excel????
>
> doctorjones_md@yahoo.com wrote:
>>
>> Does anyone know how I can resolve this issue ... I have a directory
>> which
>> contains 129 worksheets which have links to external data (in a Master
>> Spreadsheet) -- I need to copy these files into a New Directory, but kee
>> the
>> Master Spreadsheet (which they are linked to) in the original location.
>> If
>> I do a simple Cut & Past, the Reference Link to the Master Spreadsheet
>> gets
>> moved to the New Directory (where the file does not exist), but if I open
>> the worksheet (in the original directory/location) and Save As to the New
>> Directory, the worksheet saved in the New Directory maintains its link to
>> the Master Spreadsheet in the original directory/location. I hope I've
>> explained this clearly.
>>
>> Here's my problem -- it's a bit time consuming to have to open each and
>> every worksheet and Save As to the New Location -- I'm not sure if a
>> Batch
>> File (or Dos Command xcopy) would solve this -- Is there some code I
>> could
>> use to Open each worksheet, Save As to the New directory, Close, then
>> perform this on each of the .xls files in the original directory? If so,
>> could you please point me in the direction with an example of the code.
>>
>> Example:
>>
>> Files in C:\Temp (a.xls, b.xls, c.xls) Copied to C:\Budget
>>
>> Many Thanks in Advance.
>
> --
>
> Dave Peterson