#1
|
|||
|
|||
autofldupdt macro - Modifying to keep part of file name
I'm essentially in the same boat as the person in this thread: Linking excel data to Word document so links survive file namechange | Microsoft Office Forums I've got macropod's autofldupdt macro imported and it's working great (many thanks!). Starting from post #9, it looks like the modifications results in the requirement of having both the word and excel files having the same name, e.g. job4.docx and job4.xlsx. In my situation, the files all have a standard prefix of Final Report_job4.docx and Tables_job4.xlsx. How do I modify the code to reflect this? |
#2
|
|||
|
|||
So between that original link, the new code posted here, and some googling I think I've managed to get close to what I would like to achieve. The problem I'm currently having is running into
Run-Time Error '6083' Objects in this document contain links to files that cannot be found. The linked information will not be updated. at the ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)" line. I've manually added the kb 70154 registry patch that was referenced in a couple forums. Also went through and made sure all automatic update switches were removed. 1) Anything else I can try? 2) Does this macro also replace links in footers? 3) Am I on the right track with this code? Here's a snippet of the relevant sections: ... ' Set the new filename NewFile = Split(ActiveDocument.Name, ".")(0) NewFile = Replace(NewFile, "Final Report", "Tables") ... ' 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 With .LinkFormat OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "")) ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath) ' Replace the source filename with the new filename OldFile = Split(.SourceName, ".")(0) .SourceFullName = Replace(.SourceFullName, OldFile, NewFile) On Error Resume Next .AutoUpdate = False On Error GoTo 0 |
#3
|
||||
|
||||
Your code is removing the file extension from the name but you don't replace it with xlsx. I would set the name by
' Set the new filename NewFile = Replace(ActiveDocument.Name, ".docx", ".xlsx") NewFile = Replace(NewFile, "Final Report", "Tables") I assume your code includes looping through all story ranges but you haven't shown us that outer loop. If that is the case then it will hit all footers as well.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#4
|
|||
|
|||
Thanks for the extension suggestion. I've made the appropriate edit, however I'm still getting that error at the ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)" line. I've attached the edited code, unless posting the whole text here would be easier to read?
|
#5
|
||||
|
||||
Firstly, note that ActiveDocument and ThisDocument are NOT necessarily the same thing. ActiveDocument is the document that is open and has the focus. ThisDocument is the file that contains the code.
I'm not going to recreate a bunch of content to try your code and explain where it is failing but I can tell you how to debug it yourself. Change these lines: Code:
With .LinkFormat OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath) Code:
With .LinkFormat OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) Debug.Print "NewPath: " & NewPath Debug.Print "OldPath: " & OldPath Debug.Print "Source Full Name: " & .SourceFullName StrTmp = Replace(.SourceFullName, OldPath, NewPath) Debug.Print "StrTmp: " & StrTmp ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = StrTmp
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#6
|
|||
|
|||
Thank you for your help! Got it working for the most part, aside from the footer.
This is the whole loop: Code:
With ThisDocument 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 With .LinkFormat ' Replace the source filename with the new filename OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) OldFile = .SourceName StrTmp = Replace(.SourceFullName, OldPath, NewPath) StrTmp = Replace(StrTmp, OldFile, NewFile) ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = StrTmp On Error Resume Next .AutoUpdate = False On Error GoTo 0 End If End With 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 With .LinkFormat ' Replace the source filename with the new filename OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) OldFile = .SourceName StrTmp = Replace(.SourceFullName, OldPath, NewPath) StrTmp = Replace(StrTmp, OldFile, NewFile) ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = StrTmp On Error Resume Next .AutoUpdate = False On Error GoTo 0 End If End With 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 With .LinkFormat ' Replace the source filename with the new filename OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) OldFile = .SourceName StrTmp = Replace(.SourceFullName, OldPath, NewPath) StrTmp = Replace(StrTmp, OldFile, NewFile) ' Replace the link to the external file if it differs. If OldPath <> NewPath Then .SourceFullName = StrTmp On Error Resume Next .AutoUpdate = False On Error GoTo 0 End If End With End If End With Next Fld Next Rng .Save End With |
#7
|
|||
|
|||
Got it working! Ended up having to include a loop to specifically search the footers.
Code:
For j = 1 To ThisDocument.Sections.Count For j = 1 To ThisDocument.Sections.Count For k = 1 To 3 For Each Fld In ThisDocument.Sections(j).Footers(k).Range.Fields With Fld ' Skip over fields that don't have links to external files. If Not .LinkFormat Is Nothing Then With .LinkFormat ' Replace the source filename with the new filename OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\")) OldFile = .SourceName StrTmp = Replace(.SourceFullName, OldPath, NewPath) StrTmp = Replace(StrTmp, OldFile, NewFile) ' Replace the link to the external file if it differs. If .SourceFullName <> NewPath & NewFile Then .SourceFullName = StrTmp On Error Resume Next .AutoUpdate = False On Error GoTo 0 End If End With End If End With Next Fld Next k Next j |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Corrupted Footnotes After Modifying File on Macbook! | MusculaR_MinD | Word | 3 | 12-12-2019 06:09 PM |
macro to change part of the name of an existing Word file | justinmo | Word VBA | 2 | 09-11-2018 06:08 PM |
Modifying RTF File in MS Word 2007 | Nickster | Word VBA | 2 | 12-02-2015 11:33 PM |
Copying a part of a docx file as a separate file | officeboy09 | Word | 6 | 09-26-2014 05:15 PM |
modifying a PST file | ctspwc | Outlook | 5 | 11-17-2010 09:29 PM |