Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-30-2020, 02:47 PM
dinofanatic dinofanatic is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2019
Novice
autofldupdt macro - Modifying to keep part of file name
 
Join Date: Nov 2020
Posts: 5
dinofanatic is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 12-02-2020, 11:43 AM
dinofanatic dinofanatic is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2019
Novice
autofldupdt macro - Modifying to keep part of file name
 
Join Date: Nov 2020
Posts: 5
dinofanatic is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 12-02-2020, 02:47 PM
Guessed's Avatar
Guessed Guessed is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #4  
Old 12-02-2020, 03:23 PM
dinofanatic dinofanatic is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2019
Novice
autofldupdt macro - Modifying to keep part of file name
 
Join Date: Nov 2020
Posts: 5
dinofanatic is on a distinguished road
Default

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?
Attached Files
File Type: zip FieldUpdate.zip (1.9 KB, 11 views)
Reply With Quote
  #5  
Old 12-02-2020, 06:02 PM
Guessed's Avatar
Guessed Guessed is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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)
To:
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
Then step through your code or put a break point on the last line above and examine the immediate window to look at the values and determine what is going wrong.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 12-02-2020, 09:56 PM
dinofanatic dinofanatic is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2019
Novice
autofldupdt macro - Modifying to keep part of file name
 
Join Date: Nov 2020
Posts: 5
dinofanatic is on a distinguished road
Default

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
Does the footer have to be specifically addressed as its own loop?
Reply With Quote
  #7  
Old 12-07-2020, 01:25 PM
dinofanatic dinofanatic is offline autofldupdt macro - Modifying to keep part of file name Windows 10 autofldupdt macro - Modifying to keep part of file name Office 2019
Novice
autofldupdt macro - Modifying to keep part of file name
 
Join Date: Nov 2020
Posts: 5
dinofanatic is on a distinguished road
Default

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
Reply With Quote
Reply

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
autofldupdt macro - Modifying to keep part of file name 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
autofldupdt macro - Modifying to keep part of file name Copying a part of a docx file as a separate file officeboy09 Word 6 09-26-2014 05:15 PM
autofldupdt macro - Modifying to keep part of file name modifying a PST file ctspwc Outlook 5 11-17-2010 09:29 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft