Problems with .Unlink

[luggie]

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?

[macropod]

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

[luggie]

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:think: 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.

[macropod]

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.

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.

[luggie]

Thanks a lot!



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()