#1
|
|||
|
|||
Word 2007 Edit Links to Files
I have a Word document with approximately 70 links to various tables in Excel. And I know how toggle F9 to do a global search and replace on the file name nad path to change all the links relatively quickly.
Unfortunately, there are others in my office that are not as strong in Excel and Word and just can't grasp that concept. The other way I know to change all the links is via Prepare | Edit links to files. But my issue seems to be that after I selected all the links and choose change source, the dialog change source box always defaults to old the file location, and does not stay in the current directory. Thus, I need to navigate up/down a number of directories to choose the correct file each time. Would anyone know how to ensure when changing the links, the open/change source dialog box does not look to the old folder path all the time, and just stays in the current directory? |
#2
|
||||
|
||||
The dialogues will always default to whatever folder the existing link(s) point to. If these documents and their corresponding Excel workbooks are always kept in the same folder, or at least folders with the same parent/child names, you may find it useful to implement relative paths via the macro solution I've posted at:
http://windowssecrets.com/forums/sho...External-Files
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks Macropod, and you've been great help in the past.
But in this task, the user will actually be picking/choosing a different filename to link to the Word document. So not sure if the relevant path code will do the trick. I came across this code that does most of what I'm looking for. But I need to modify it to replace the folder path and file name separately (I think). This is because Excel graphs that are OLE links in word show their LINK code slightly different. I think there inlineshapes? So I'm trying to modify the below to replace the link file path and link file name separately. But the 'FileName' variable is still showing the full path and name, and not the name only. The original code is by 'theDrew' from MrExcel forums back in 2011. So thanks to him as well in getting me started Code:
Public Sub changeSource() Dim dlgSelectFile As FileDialog 'FileDialog object ' Dim thisField As Field Dim selectedFile, FileName As Variant 'must be Variant to contain filepath of selected item Dim newFile As Variant Dim fieldCount As Integer ' Dim x As Long On Error GoTo LinkError 'create FileDialog object as File Picker dialog box Set dlgSelectFile = Application.FileDialog(msoFileDialogFilePicker) With dlgSelectFile .InitialFileName = ActiveDocument.Path .Filters.Clear 'clear filters .Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'filter for only Excel files 'use Show method to display File Picker dialog box and return user's action If .Show = -1 Then 'step through each string in the FileDialogSelectedItems collection For Each selectedFile In .SelectedItems newFile = selectedFile 'gets new filepath FileName = .SelectedItems.Item(1) Next selectedFile Else 'user clicked cancel Exit Sub End If End With MsgBox "Selected item's Full Path and Name: " & newFile MsgBox "The file name only is: " & FileName ' Set dlgSelectFile = Nothing ' 'update fields ' With ActiveDocument ' fieldCount = .Fields.Count ' For x = 1 To fieldCount ' With .Fields(x) ' 'Debug.Print x ' ' Debug.Print .Type ' If .Type = 56 Then ' 'only update Excel links. Type 56 is an excel link ' .LinkFormat.SourceFullName = newFile ' ' .Update ' .LinkFormat.AutoUpdate = False ' DoEvents ' End If ' End With ' Next x ' End With MsgBox "Source data has been successfully imported." Exit Sub LinkError: Select Case Err.Number Case 5391 'could not find associated Range Name MsgBox "Could not find the associated Excel Range Name " & _ "for one or more links in this document. " & _ "Please be sure that you have selected a valid " & _ "Quote Submission input file.", vbCritical Case Else MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical End Select End Sub |
#4
|
||||
|
||||
If, for a given file, the links only point to a single workbook, the code in the link I posted could be adapted for that fairly easily.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks Macropod
And please forgive me as I am still learning. I'm working to modify the relevant path macro code to work based off the user picking an existing file. Keep getting stuck in I think an infinite loop that I need to work out!! But I'm still struggling with how you can separate out the File Path and File name Separately. I believe I need to do this for Graphs that are OLE linked to word. The LINK code for those looks something like the below: Note: This is with Office 2007. I'll fix it all to Office 2013 in a month when our Company upgrades. Code:
{ LINK Excel.Sheet.8 "C:\\Data\\_Template\\Template Workpapers.xltm" "Receivable Charts![Template Workpapers.xltm]Receivable Charts Chart 4" \p } So I think for those graphs, I need to replace the filename separately. Currently I do a global search and replace on the file path and file name separately. |
#6
|
|||
|
|||
I think I'm making some progress and trying to replace the FilePath and FileName Separately, again as I think that is needed due to the charts needing the FileName twice in the link path.
Some of the code I found on line (Public FileName), but I've found the SourcePath and Sourcename are read only, and can't seem to replace them? Code:
Sub ChangeFileLinks() Dim f As Object Dim i, x, fieldCount As Long Dim OldPath, OldFile As String Dim NewPath, NewFile As String Set f = Application.FileDialog(3) f.AllowMultiSelect = False If f.Show Then For i = 1 To f.SelectedItems.Count 'Get the File Path Only MsgBox "The New File Path is: " & f.InitialFileName NewPath = f.InitialFileName & "\" 'Get the FileName only. Uses Public FileName Function Below MsgBox "The FileName Only is: " & Filename(f.SelectedItems(i)) NewFile = Filename(f.SelectedItems(i)) Next End If With ActiveDocument fieldCount = .Fields.Count For x = 1 To fieldCount With .Fields(x) 'Debug.Print .Type If .Type = 56 Then 'Get The Existing FilePath and File Name from the Link Sources 'MsgBox "The Existing FilePath is: " & .LinkFormat.SourcePath OldPath = .LinkFormat.SourcePath 'MsgBox "The Existing File Name is: " & .LinkFormat.SourceName OldFile = .LinkFormat.SourceName 'Replace the FilePath '.LinkFormat.SourcePath = NewPath 'Replace the FileName '.LinkFormat.SourceName = NewFile .Update End If End With Next x End With End Sub Public Function Filename(ByVal strPath As String) As String If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then Filename = Filename(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1) End If End Function |
#7
|
||||
|
||||
Try:
Code:
Sub UpdateLinks() ' This routine updates all external links, pointing them all to a single selected file. ' Turn Off Screen Updating temporarily. Application.ScreenUpdating = False Dim TrkStatus As Boolean, Pwd As String, pState As Boolean, StrFlNm As String Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape ' Select the new source file for the links With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker) .Title = "Select the new source file" .InitialFileName = ActiveDocument.Path .Filters.Clear 'clear filters .Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'filter for only Excel files .AllowMultiSelect = False If .Show = -1 Then StrFlNm = .SelectedItems(1) Else MsgBox "No new source file selected. Exiting", vbExclamation Exit Sub End If End With With ActiveDocument ' If used, insert your document's password between the double quotes on the next line Pwd = "" ' Initialise the protection state pState = False ' If the document is protected, unprotect it If .ProtectionType <> wdNoProtection Then ' Update the protection state pState = True ' Unprotect the document .Unprotect Pwd End If ' Store current Track Changes status, then switch off temporarily. TrkStatus = .TrackRevisions .TrackRevisions = False For Each Rng In .StoryRanges ' Go through the shapes in the story range. For Each Shp In Rng.ShapeRange With Shp ' Skip over shapes that don't have links to external files. If Not .LinkFormat Is Nothing Then ' Replace the link to the external file. .LinkFormat.SourceFullName = StrFlNm End If End With Next Shp ' Go through the inlineshapes in the story range. For Each iShp In Rng.InlineShapes With iShp ' Skip over inlineshapes that don't have links to external files. If Not .LinkFormat Is Nothing Then ' Replace the link to the external file. .LinkFormat.SourceFullName = StrFlNm End If End With Next iShp ' Go through the fields in the story range. For Each Fld In Rng.Fields With Fld ' Skip over fields that don't have links to external files. If Not .LinkFormat Is Nothing Then ' Replace the link to the external file. .LinkFormat.SourceFullName = StrFlNm End If End With Next Fld Next Rng ' Restore original Track Changes status ActiveDocument.TrackRevisions = TrkStatus ' If the document was protected, reprotect it, preserving any formfield contents If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd ' Set the saved status of the document to true, so that changes via ' this code are ignored. Since the same changes will be made the ' next time the document is opened, saving them doesn't matter. .Save End With ' Restore Screen Updating Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Macropod.
I'm going to play more with the code you just posted, but I found an old post (by you actually ) that used code.text in the replace. your old post http://www.wordbanter.com/showthread.php?t=76379 So I am having success with the below code currently. The below will prompt for the new File and replace the FilePath and FileName Separately. It's pretty 'quick', replacing about 70 links in a few seconds. Code:
Sub ChangeFileLinks() Dim f As Object Dim i, x, fieldCount As Long Dim OldPath, OldFile As String Dim NewPath, NewFile As String Set f = Application.FileDialog(3) f.AllowMultiSelect = False If f.Show Then For i = 1 To f.SelectedItems.Count 'Get the File Path Only NewPath = f.InitialFileName NewPath = Replace(NewPath, "\", "\\") MsgBox "The New File Path is: " & NewPath 'Get the FileName only. Uses Public FileName Function Below MsgBox "The FileName Only is: " & Filename(f.SelectedItems(i)) NewFile = Filename(f.SelectedItems(i)) Next End If With ActiveDocument fieldCount = .Fields.Count For x = 1 To fieldCount With .Fields(x) 'Debug.Print .Type If .Type = 56 Then 'Get The Existing FilePath and File Name from the Link Sources OldPath = .LinkFormat.SourcePath & "\" OldPath = Replace(OldPath, "\", "\\") 'MsgBox "The Existing FilePath is: " & OldPath OldFile = .LinkFormat.SourceName 'MsgBox "The Existing File Name is: " & .LinkFormat.SourceName 'Replace the FilePath ' Replace the link to the external file .Code.Text = Replace(.Code.Text, OldPath, NewPath) '.LinkFormat.SourceFullName = NewPath 'Replace the FileName '.LinkFormat.SourceName = NewFile .Code.Text = Replace(.Code.Text, OldFile, NewFile) .Update End If End With Next x End With End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to fix/edit corrupted References in Word 2007 XP sv pk 3 | Hyper-Energy Bob | Word | 3 | 07-08-2014 02:00 PM |
How to copy linked Excel and Word files and retain links | ashleynpeters1 | Word | 1 | 05-30-2013 02:25 PM |
Edit text in a picture on word 2007 | btmna | Word | 2 | 01-31-2013 11:28 AM |
Edit Links Command Selection | nkg | Word | 1 | 02-19-2012 07:21 PM |
Batch Edit Links | tosti | PowerPoint | 5 | 01-31-2012 12:51 PM |