|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
You could adapt the code posted in the other thread to ChangeLink
rather than UpdateLink
So, instead of
Sub UpdateActiveWorkbookLinks()
make it
Sub ChangeMasterWorkbookLink()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect
Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
If Instr(LCase(vLinkSources(iLinkSource), "master.xls")>0 Then
ActiveWorkbook.ChangeLink vLinkSources(iLinkSource), _
"\\Server\MyDir\mySubDir\master.xls" , xlExcelLinks
End If
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Protect
Password:="mypassword"
Next
End Sub
and call it from the code that loops through all xls files in a
directory.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|
|
|