更改 Word 文档中所有链接的来源 - 范围错位 [英] Change all links' sources in a Word document - Misplacement of Ranges

问题描述

我处理此代码以将 Word 模板中所有链接的字段/图表/...的来源更改为启动它的工作簿.

I work on this code to change the sources of all linked Fields/Charts/... in Word templates to the workbook it is launched from.

我有常用字段和图表(它们存储在InlineShapes中)，所以每个模板都有2个循环.

I had usual fields and charts (which are stored in InlineShapes), so I have 2 loops for every template.

这些循环有时会停留在 For Each 上，并继续在 Fields/InlineShapes 上循环(甚至不增加索引...) 不停止.(我为此添加了 DoEvents，它似乎降低了发生这种情况的频率...如果您有解释，我们将非常欢迎！)

These loops sometimes stays stuck with For Each, and keep looping on Fields/InlineShapes (and don't even increase index...) without stopping. (I added the DoEvents for that, and it seems to reduce the frequency of that happening... if you have an explanation, it'll be very welcome!)

并且使用 For i = ... to .Count，现在它可以完美运行，除了 Pasted Excel Range 已更改为一个范围大小相同，每次从 A1 开始，并在工作簿的活动工作表上.

And with For i = ... to .Count, now it works pretty much flawlessly, except for Pasted Excel Range which are changed to a range of the same size, starting on A1 each time, and on the active sheet of the workbook.

为了避免 InlineShapes 出现问题，我添加了一个测试来了解 LinkFormat.SourceFullName 是否可访问，从而避免出现会停止进程的错误:

To avoid problems with InlineShapes, I added a test to know if the LinkFormat.SourceFullName is accessible and therefore avoid an error that would stop the process :

Function GetSourceInfo(oShp As InlineShape) As Boolean
Dim test As Variant
    On Error GoTo Error_GetSourceInfo
    test = oShp.LinkFormat.SourceFullName
    GetSourceInfo = True
    Exit Function
Error_GetSourceInfo:
   GetSourceInfo = False
End Function

<小时>

我注意到我的模板中有 2 种类型的链接 InlineShapes :

粘贴为 Microsoft Office 图形对象:.hasChart = -1.Type = 12.LinkFormat.Type = 8

Pasted as Microsoft Office Graphic Object : .hasChart = -1 .Type = 12 .LinkFormat.Type = 8

粘贴为 图片(Windows 图元文件):.hasChart = 0.Type = 2.LinkFormat.Type = 0

Pasted as Picture (Windows Metafile) : .hasChart = 0 .Type = 2 .LinkFormat.Type = 0

这是我的 InlineShapes 循环:

For i = 1 To isCt
    If Not GetSourceInfo(oDoc.InlineShapes(i)) Then GoTo nextshape
        oDoc.InlineShapes(i).LinkFormat.SourceFullName = NewLink
        DoEvents
nextshape:
Next i

问题

因为我只更新了 .SourceFullName，它只描述了路径和文件，我不知道为什么或如何影响最初选择的范围......

Question

As I only update the .SourceFullName, which only describe Path and File, I have no clue on why or how this affect the initially selected range...

问题回顾:粘贴的 Excel 范围 更改为相同大小的范围，每次从 A1 开始，并在工作簿.

Problem recap : Pasted Excel Range which are changed to a range of the same size, starting on A1 each time, and on the active sheet of the workbook.

关于如何更新 Word 链接的任何其他输入将不胜感激！

And any other inputs on how to update Word links will be appreciated!

正如Andrew Toomey 的回答中所建议的，我使用了超链接，但在我的每个模板中，该集合都是空的:

As suggested in Andrew Toomey's answer, I worked with HyperLinks but in each one of my templates, the collection is empty :

我尝试了很多不同的组合，这是我清理过的:

I've tried quite a lot of different combinations and here is what I cleaned :

Sub change_Templ_Args()

Dim oW As Word.Application, _
    oDoc As Word.Document, _
    aField As Field, _
    fCt As Integer, _
    isCt As Integer, _
    NewLink As String, _
    NewFile As String, _
    BasePath As String, _
    aSh As Word.Shape, _
    aIs As Word.InlineShape, _
    TotalType As String

On Error Resume Next
Set oW = GetObject(, "Word.Application")
If Err.Number <> 0 Then Set oW = CreateObject("Word.Application")
On Error GoTo 0
oW.Visible = True

NewLink = ThisWorkbook.Path & "" & ThisWorkbook.Name

BasePath = ThisWorkbook.Path & "\_Templates"
NewFile = Dir(BasePath & "*.docx")

Do While NewFile <> vbNullString
    Set oDoc = oW.Documents.Open(BasePath & NewFile)
    fCt = oDoc.Fields.Count
    isCt = oDoc.InlineShapes.Count
    MsgBox NewFile & Chr(13) & "Fields : " & oDoc.Fields.Count & Chr(13) & "Inline Shapes : " & isCt

    For i = 1 to fCt 
        With oDoc.Fields(i)
            '.LinkFormat.AutoUpdate = False
            'DoEvents
            .LinkFormat.SourceFullName = NewLink
            '.Code.Text = Replace(.Code.Text, Replace(.LinkFormat.SourceFullName, "", "\"), Replace(NewLink, "", "\"))
        End With
    Next i

    For i = 1 To isCt
        If Not GetSourceInfo(oDoc.InlineShapes(i)) Then GoTo nextshape
            With oDoc.InlineShapes(i)
                .LinkFormat.SourceFullName = NewLink
                DoEvents
                'MsgBox .LinkFormat.SourceFullName & Chr(13) & Chr(13) & _
                        "Type | LF : " & .LinkFormat.Type & Chr(13) & _
                        "Type | IS : " & .Type & Chr(13) & _
                        "hasChart : " & .HasChart & Chr(13) & Chr(13) & _
                        Round((i / isCt) * 100, 0) & " %" 
            End With
nextshape:
    Next i

    MsgBox oDoc.Name & " is now linked with this workbook!"
    oDoc.Save
    oDoc.Close
    NewFile = Dir()
Loop
oW.Quit

Set oW = Nothing
Set oDoc = Nothing

MsgBox "All changes done.", vbInformation + vbOKOnly, "End proc"

End Sub

推荐答案

我认为使用 hyperlinks 集合是解决方案的关键 - 除非您有特定的理由不这样做.从 Word 文档到 Excel 工作簿的链接是外部链接，因此都应列在 Hyperlinks 集合中(无论它们是文本链接还是链接的 InlineShapes).

I think using the hyperlinks collection is the key to your solution - unless you have a specific reason not to. Links from a Word document to an Excel workbook are external links so should all be listed in the Hyperlinks collection (regardless of whether they are text links or InlineShapes that are linked).

这是我的代码，可能会有所帮助.为简单起见，我对 Word 文档进行了硬编码，因为这对您来说不是问题:

Here's my code that may be of some help. For simplicity I've hard coded the Word document since that's not an issue for you:

Sub change_Templ_Args()
    WbkFullname = ActiveWorkbook.FullName

    'Alternatively...
    'WbkFullname = "C:	empmyworkbook.xlsx"
    'Application.Workbooks.Open Filename:=WbkFullname

    'Get Document filename string
    MyWordDoc = "CTempmysample.docx"

    Set oW = CreateObject("Word.Application")
    oW.Documents.Open Filename:=MyWordDoc 
    Set oDoc = oW.ActiveDocument

    'Reset Hyperlinks
    For Each HypLnk In oDoc.Hyperlinks
        HypLnk.Address = WbkFullname
    Next

End Sub

如果你真的需要使用 Fields 和 InlineShapes 试试这个代码.我在 For 循环中使用了变体，并为目录或交叉引用字段的字段添加了对 wdLinkTypeReference 的检查 - 这些链接是文档内部的.

If you really need to use Fields and InlineShapes try this code. I've used variants in For loop and added a check for wdLinkTypeReference for fields that are Table of Contents or Cross Reference fields - these links are internal to the document.

'Reset links to InlineShapes
For Each InShp In ActiveDocument.InlineShapes
    If Not InShp.LinkFormat Is Nothing Then
        InShp.LinkFormat.SourceFullName = WbkFullname
    End If
    If InShp.Hyperlink.Address <> "" Then
        InShp.LinkFormat.SourceFullName = WbkFullname
    End If
Next

'Reset links to fields
For Each Fld In ActiveDocument.Fields
    If Not Fld.LinkFormat Is Nothing Then
        If Fld.LinkFormat.Type <> wdLinkTypeReference Then 
            Fld.LinkFormat.SourceFullName = WbkFullname
        End If
    End If
Next

这篇关于更改 Word 文档中所有链接的来源 - 范围错位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！