Consulting

Results 1 to 5 of 5

Thread: Problems with .Unlink

  1. #1
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location

    Problems with .Unlink

    I have an excel document with various sheets, containing different tables that I'd need to copy to a
    word document. For doing so I created links to tables in an excel sheet, that are updated to the
    new path of the excel sheet during this routine:

    Sub ChangeLinks()
        Dim fld As Field
        For Each fld In ThisDocument.Fields
            If fld.Type = wdFieldLink Then
                If fld.LinkFormat.SourceFullName Like "PATTERN*" Then
                    fld.Update
                    fld.Unlink
                    Exit For
                End If
            End If
        Next fld
    End Sub
    As you can see, I'm also trying to unlink these links after updating them to avoid
    problems. This however doesn't work.
    Anyone knows why?
    Also: Is there a better way to copy tables from excel to specific points within a existing
    word document than the way I do it here?

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    It doesn't look to me like you're changing any links, merely refreshing them before unlinking. Two observations:
    1. Your code refers to 'ThisDocument', which means it runs against the document or template containing the code, not the active document; and
    2. Your code exits after processing the first applicable field it finds.
    Try:
    Sub Unlink()
    Application.ScreenUpdating = False
    Dim i As Long
    With ActiveDocument
      For i = .Fields.Count To 1 Step -1
        With .Fields(i)
          If .Type = wdFieldLink Then
            If .LinkFormat.SourceFullName Like "PATTERN*" Then
              .Update
              .Unlink
            End If
          End If
        End With
      Next
    End With
    Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 06-16-2020 at 03:53 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location
    Okay. It seems that I'd need to explain what I'm trying to do in a little more detail:
    I have python programm which creates an excel file with several tables and a word template file with empty tables.
    The latter's tables are wdFieldLinks pointing to nowhere (the link simply says:
    {LINK Excel.Sheet.12 "PATTERN" "SHEETNAME!TABLE_INDEX" \a \f 4 \h}
    ). The makro I wrote is called from the python programm,
    therefore there's no activeDocument (when I try it with activeDocument, it says that there's no document available) and I need to use ThisDocument (or another thing that I don't know of). However it doesn't work.


    Sub ChangeLinks(path_docx, path_xlsx)
        
        Documents(path_docx).Activate
    
        With ActiveDocument
            For Each f In .Fields
                If f.Type = wdFieldLink Then
                    f.LinkFormat.SourceFullName = path_xlsx
                    f.Update
                End If
            Next
        End With
      
        ActiveDocument.Fields.Unlink
    
    End Sub
    Altogether, the tables from the excel file should be linked to the place holders in the word file. Then they should be unlinked and the excel file gets deleted (from python).

    The problem comes from the links' SourceFullName when trying to set them because their initial links points to nowhere which vba doesn't approve. I cannot put a legal link as the word templates containing them have nothing to be linked to as the excel file are created later. Another reason is that the program should be available from different computers, so putting any generic legal link is impossible There should be something like a "don't check if the existing link are legal, just (force) set them. Why should it care for it anyway? Especially in the very instant when it is set...

    Another approach that I'm thinking of, would be to create those wdFieldLinks replacing certain anker points in the word template such as specific words like "TABLE_ANKER_x" but I don't know how to do this.
    Last edited by luggie; 06-16-2020 at 10:15 AM.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by luggie View Post
    I have python programm which creates an excel file with several tables and a word template file with empty tables.
    The latter's tables are wdFieldLinks pointing to nowhere (the link simply says:
    {LINK Excel.Sheet.12 "PATTERN" "SHEETNAME!TABLE_INDEX" \a \f 4 \h}
    ).The makro I wrote is called from the python programm,
    therefore there's no activeDocument (when I try it with activeDocument, it says that there's no document available) and I need to use ThisDocument (or another thing that I don't know of). However it doesn't work.
    It is impossible for you to open a Word document or template for editing and not have an ActiveDocument. The fundamental problem problem with your code is that your ActiveDocument reference lacks an application context. Presumably, somewhere you have instantiated the Word application (e.g. as wdObj, or something such), in which case you need to refer to wdObj.ActiveDocument.
    Quote Originally Posted by luggie View Post
    The problem comes from the links' SourceFullName when trying to set them because their initial links points to nowhere which vba doesn't approve. I cannot put a legal link as the word templates containing them have nothing to be linked to as the excel file are created later. Another reason is that the program should be available from different computers, so putting any generic legal link is impossible There should be something like a "don't check if the existing link are legal, just (force) set them. Why should it care for it anyway? Especially in the very instant when it is set...
    Neither VBA nor any other programming language is likely to care whether the the field contains an invalid link. Most likely, the problem is due to the fact "PATTERN" doesn't even look like a link - valid or otherwise. In any event, updating SourceFullName is not going to address what appears to be another invalid reference - to "SHEETNAME!TABLE_INDEX"
    You might do better to start off with:
    {LINK "FILEPATH" "REFERENCE" \a \f 4 \h}
    and use something like:
    Sub SetLinks(path_docx, path_xlsx, ref_xlsx)
    Set wdDoc = wdObj.Documents(path_docx).Open
    With wdDoc
      For Each Fld In .Fields
        With Fld
          If .Type = wdFieldLink Then
            .Code.Text = Replace(.Code.Text, "FILEPATH", path_xlsx)
            .Code.Text = Replace(.Code.Text, "REFERENCE", ref_xlsx)
            .Update
          End If
        End With
      Next
      .Fields.Unlink
    End With
    End Sub
    bearing in mind the need to use either \\ or / as the path separators in path_xlsx and that both path_xlsx and ref_xlsx must include double quotes at each end.
    Last edited by macropod; 06-16-2020 at 03:52 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location
    Thanks a lot!

    Quote Originally Posted by macropod View Post
    bearing in mind the need to use either \\ or / as the path separators in path_xlsx and that both path_xlsx and ref_xlsx must include double quotes at each end.
    Especially this did the trick. It was a little difficult to persuade python to parse explicit double backslash as argument.

    As I was struggeling with this for a while, I'll leave my final macro here:
    Sub AutoOpen()
    
        Word.Options.UpdateLinksAtOpen = False
        
    End Sub
    Sub UpdateLinks(path_docx, path_xlsx)
        
        Documents(path_docx).Activate
        
        With ActiveDocument
            For Each fld In .Fields
            With fld
              If .Type = wdFieldLink Then
                .Code.Text = Replace(.Code.Text, "PATH", path_xlsx)
                .Update
              End If
            End With
          Next
          
          .Fields.Unlink
        End With
    
    End Sub
    The dummy links look like:

    { LINK Excel.Sheet.12 "PATH" "MySheetName!R1C1:R17C6" \a \f 4 \h}
    The Python code running the macro:

    import win32com.client as win32
    
    word = win32.Dispatch('Word.Application')
    
    # run macro
    wordDoc = word.Documents.Open(path, False, False, False)
    word_path = word_path.replace("/", "\\")         # for some reason vba expects different
    excel_path = excel_path.replace("/", "\\\\")    # backslashes for .Activate and in FieldLinks
    word.Run("UpdateLinks", word_path, excel_path)                 
    
    # save doc
    wordDoc.SaveAs2(path, FileFormat=16)
    wordDoc.Close()
    word.Quit()

Posting Permissions

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