Page 16 of 17 FirstFirst ... 614151617 LastLast
Results 226 to 240 of 243
  1. #226
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    Hi all,

    I've been OS for 3 months, hence the delay in replying. Have you resolved the issue, or do you still need help?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. #227
    New Lounger
    Join Date
    Aug 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I could still use some help. When I first posted I was using 2007. Now I have a new machine and am running 2013 and still have the same issue

  3. #228
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    You might try replacing the 'UpdateFields' routine with:
    Code:
    Private Sub UpdateFields()
    ' This routine sets the new path for external links, pointing them to the current folder.
    Dim oRng As Range, oFld As Field, i As Integer
    Dim OldPath As String, NewPath As String, Parent As String, Child As String
    ' Set the new path.
    ' If your files are always in a folder whose path bracnhes off, one or more levels above the current
    ' folder, replace the second '0' on the next line with the number of levels above the current folder.
    For i = 0 To UBound(Split(ActiveDocument.Path, "\")) - 0
      Parent = Parent & Split(ActiveDocument.Path, "\")(i) & "\"
    Next i
    ' If your files are in a Child folder below the (new) parent folder, add the Child folder's
    ' path from the parent (minus the leading & trailing "\" path separators) on the next line.
    Child = ""
    NewPath = Parent & Child
    ' Strip off any trailing path separators.
    While Right(NewPath, 1) = "\"
      NewPath = Left(NewPath, Len(NewPath) - 1)
    Wend
    ' Go through all story ranges in the document, including shapes, headers & footers.
    With ThisDocument
      For Each oRng In .StoryRanges
        ' Go through the fields in the story range.
        For Each oFld In oRng.Fields
            With oFld
              ' Skip over fields that don't have links to external files.
              If Not .LinkFormat Is Nothing Then
                With .LinkFormat
                  OldPath = .SourcePath
                  ' Replace the link to the external file if they differ.
                  If OldPath <> NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
                End With
              End If
            End With
        Next oFld
      Next oRng
    End With
    End Sub
    Caveat: At one time, Word had a bug that meant testing .LinkFormat and retrieving .SourcePath were both unreliable. I don't know if that bug has been fixed and I have nothing to test it with.
    Last edited by macropod; 2013-10-04 at 20:33.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #229
    New Lounger
    Join Date
    Aug 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul,

    First off, thanks to you and anyone else that's contributed toward this effort. You're filling a much needed niche.

    My aim is to use VBA in a Word document to, update upon opening, linked data from one or more spreadsheets after copying and moving the Word and Excel files to a new folder in same relative folder tree relationship as the original. Unless I'm waaay off base your code does exactly that. Great

    My problem was originally that the code "seemingly" wouldn't run at all. I say "seemingly" because I think it was actually running but finding no "valid" links there's nothing to update so it seems to finish without changing anything.

    And I think the code is not finding any "valid" links in the Word document because of the peculiar, to me anyway, way Word is changing the link layout between opening, updating, and saving the Word document.

    The attached images should show what I'm experiencing...

    When I open a Word document containing a link to a Spreadsheet, the link is formatted as such... which doesn't look like a properly formatted link to me.
    Link layout upon Opening a Saved .docm.png

    However, when I update the link the formatting changes to the following... which does look like a properly formatted link to me.
    Link layout after Updating.png

    You can see that the link formatting changes upon updating and saving.
    Consequently when the document opens from it's last save the link is not formatted accurately and the UpdateFields code runs but doesn't change anything as it detects no "valid" links that it should act upon.

    If I update the link so that the link formatting changes and then run the UpdateFields manually I can see that the code is attempting to re-write the path, but I get an infinite loop where the code continually attempts to re-write the same link but I think that's further down the troubleshooting path.

    Question: Is the link "reformatting" between opening, updating, and saving; normal and expected behavior for Office 2010?
    Last edited by Kayem68; 2014-08-06 at 13:56.

  5. #230
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    Hi Kaye,

    The first link image is indeed incorrect. A valid link has four parts -
    1. The LINK descriptor and file format
    2. The file's full name
    3. The addess within the file
    4. formatting switches.

    Conversely, the second link image is correct.

    From what you describe, what is happening after updating is that parts 2 & 3 are being split into their correct components. On my system, a newly-created link is already correctly formatted. The only difference I can see is that I'm not using a network, but why linking across a network should cause a malformed link I don't know.

    Since you say that updating the link causes it to reformat itself correctly, the solution may be to issue an .Update command before & after the path updating. In other words:
    Code:
              If Not .LinkFormat Is Nothing Then
                .Update
                With .LinkFormat
                  OldPath = .SourcePath
                  ' Replace the link to the external file if they differ.
                  If OldPath <> NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
                End With
                .Update
              End If
    Doubtless this change will make the code run somewhat slower.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #231
    New Lounger
    Join Date
    Aug 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I'm still having trouble with the code whereby it will update the source of the direct cell references and tables in Word, but not the pie charts, etc.

    Is there any solution to this?

    Thanks

  7. #232
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    How are the charts inserted? Are they inserted as fields, or as embedded objects with their own links back to the source data? Can you attach a document to a post with a representative chart? You do this via the paperclip symbol on the 'Go Advanced' tab.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #233
    New Lounger
    Join Date
    Aug 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    How are the charts inserted? Are they inserted as fields, or as embedded objects with their own links back to the source data? Can you attach a document to a post with a representative chart? You do this via the paperclip symbol on the 'Go Advanced' tab.
    They were pasted from the excel document using the paste option to link back to the excel document they relate to - ie F9 still refreshes their data from the workbook.

  9. #234
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    OK, but a document demonstrating the issue as requested (so I can examine the field coding, etc), would be helpful.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #235
    New Lounger
    Join Date
    Feb 2019
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Excel file gives trouble: cannot open two excel files at the same time

    I know this problem has been adressed before but I suspect a different reason for this error.

    Situation: Word document with relative path code as a module plus a linked Excel workbook.

    If I copy the word and excel file to a new folder, and opening the Word file from it's new location it throws an error: "cannot open two workbooks with the same name".
    If I rename the Excel file in the old location, the error does not appear. This suggests that Word opens both the old and the new excel file resulting in the Error.
    I am sure that the code is not in the normal Template (and tehrefore executed twice).

    Any suggestions on solutions?

  11. #236
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    As has been suggested before (see https://forums.windowssecrets.com/sh...=1#post1099410) you should be able to resolve that by turning off the 'update automatic links at open' option (File|Options|Advanced>General).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #237
    New Lounger
    Join Date
    Feb 2019
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi apologies if this has been asked before but is there a workaround to linking to an Excel file on Onedrive?

    Macropod's code works beautifully on locally stored files but I can't get it to work on the file stored on one drive.

    When linking from the workbook this is the link path that is shown:

    { LINK Excel.SheetMacroEnabled.12 "file://d.docs.live.net/9661871e62308fe0/Documents/Hylands Homes/Quotes - HH/Estimates/_TEMPLATE_FOLDER/test.xlsm" "E Output!R9C2" \a \t }

    This is what I have tried to no avail:

    { LINK Excel.SheetMacroEnabled.12 " D:\OneDrive\Documents\Hylands Homes\Quote - HH\Estimates\_TEMPLATE_FOLDER\test.xlsm " "E Output!R9C2" \a \t }

    { LINK Excel.SheetMacroEnabled.12 " D:\\OneDrive\\Documents\\Hylands Homes\\Quote - HH\\Estimates\\_TEMPLATE_FOLDER\\test.xlsm " "E Output!R9C2" \a \t } which is what the locally stored path looks like

    Any help would be appreciated, even if it is just to say it has been covered and I'll start trawling the last 16 pages to find it.

  13. #238
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    If you're linking to a file stored on OneDrive, why would you need to make the path relative. More specifically, to what would it be relative?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #239
    New Lounger
    Join Date
    Feb 2019
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    If you're linking to a file stored on OneDrive, why would you need to make the path relative. More specifically, to what would it be relative?
    Hi Macropod. The Excel and Word files are in the same "template" folder on my OneDrive. Each new job that comes in is added to my system by copy-pasting the template folder, renaming it as the job reference and then editing its contents to suit the job requirements. The excel workbook is an estimating spreadsheet which is linked by the word document.

    The word document in the newly created copied folder still links to the Excel workbook in the template folder. Instead, I want it to link to the workbook that is sitting next to it in the copied folder hence the relative path rather than the dynamic path.

    This all works as you'd expect when the files are stored conventionally on a local hard drive but the second you link to an excel file on OneDrive it swaps out C:/Onedrive/Documents etc to the https:// internet pathway instead. What I was trying to achieve was linking to the excel file using a conventional pathway rather than this web path that seems to break your code.

  15. #240
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,255
    Thanks
    2
    Thanked 484 Times in 393 Posts
    Try replacing the UpdateFields macro with:
    Code:
    Private Sub UpdateFields()
    ' This routine sets the new path for external links, pointing them to the current folder.
    Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long, NewPath As String
    ' Go through all story ranges in the document.
    With ThisDocument
      NewPath = .Path & "\"
      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
                  .SourceFullName = NewPath & .SourceName
                  On Error Resume Next
                  .AutoUpdate = False
                  On Error GoTo 0
              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
                  .SourceFullName = NewPath & .SourceName
                  On Error Resume Next
                  .AutoUpdate = False
                  On Error GoTo 0
              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
                  .SourceFullName = NewPath & .SourceName
                  On Error Resume Next
                  .AutoUpdate = False
                  On Error GoTo 0
              End With
            End If
          End With
        Next Fld
      Next Rng
      .Save
    End With
    End Sub
    Last edited by macropod; 2019-02-15 at 16:06.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 16 of 17 FirstFirst ... 614151617 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •