Macro to change file path

John Pryor · #1 07-17-2018, 02:03 AM

Hi Again.

I have a question about creating some means of finding the filepath of existing links to one excel document and replacing with the filepath of a new source document. My document has over 200 links so using the standard links management tool would take forever.

Essentially, I would like to create some sort of useform that would allow me to browse files on my computer and select the appropriate excel workbook to redirect the links to.

I appreciate this may be a huge ask, but even a step in the right direction would be helpful.

I am a novice at VBA in word and a beginner on VBA in excel, so my knowledge is basic.

Thank you!

Guessed · #2 07-17-2018, 04:10 AM

If you press Alt-F9 do all the links show up as field codes? If so, you can do a search and replace to amend the relevant part of the file path. Pay careful attention to the double backslashes to match the syntax correctly.

John Pryor · #3 07-17-2018, 04:34 AM

Thanks Guessed.
That is what I do currently but its a bit long winded as a workaround.
I would ideally like to make an idiot proof link tool that others can also use.
I know there must be some code that I can use to make the re-linking of excel a straightforward process without potential for syntax errors.
Cheers,
John.

John Pryor · #4 07-17-2018, 11:24 AM

I have been searching the web and note in a previous post on another forum, Macropod proposed the following code to assist someone else with a similar problem here:
https://www.msofficeforums.com/word/...nal-files.html

I cant get it working and wonder if there is anything that jumps out as problematic (I am on office 365?

Thank you in advance...

Quote:

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:=msoFileDial ogFilePicker)
.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

macropod · #5 07-17-2018, 03:41 PM

Since the code works just fine, you'll need to explain what problems you're having getting it to work...

John Pryor · #6 07-26-2018, 05:23 AM

Hi Macropod.
What happens is nothing. I get the blue spinning wheel then excel freezes. I have to crash out of it to be able to carry on with what I am doing.
I have 270 links in the document to the excel source book. Perhaps that is too many for this application?
Cheers,
John

macropod · #7 07-26-2018, 03:02 PM

It's not that there are too many, it's that updating so many links takes a long time. You might try:
a) inserting -
DoEvents
after each -
.LinkFormat.SourceFullName = StrFlNm
and editing the registry, as described in https://www.msofficeforums.com/word-...tml#post130308 if you haven't already done so.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I use as Save As to take the name from a text and change the file path?	rosscortb	Word VBA	5	03-08-2018 04:30 AM
Change old path to new path (batch)	NobodysPerfect	Word VBA	2	08-14-2014 10:09 PM
Change path to movie clip?	triumphx1	PowerPoint	1	12-05-2010 03:55 PM
Change Action Setting Path	gskelton	PowerPoint	0	02-27-2010 03:20 PM
How i can change default signature path?	d4rqu	Outlook	0	10-20-2009 02:08 AM