Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Problems saving a worksheet with 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. |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Problems saving a worksheet with links
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
History worksheet - can it remain viewable after saving? | Excel Worksheet Functions | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
Updating links from one worksheet to another worksheet | Excel Worksheet Functions | |||
saving atable of links into spreadsheet | Excel Discussion (Misc queries) | |||
worksheet links | Excel Worksheet Functions |