这两天抽空写了些VBA脚本，遇到一个Case，需要将Excel中的内容导出到Word中。正常情况下，需要加工程里手动添加引用。Google一番后，找到了一种无需手动添加额外引用的办法：https://stackoverflow.com/a/42228424

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Sub AddReferences(wbk As Workbook)
    ' Run DebugPrintExistingRefs in the immediate pane, to show guids of existing references
    AddRef wbk, "{00025E01-0000-0000-C000-000000000046}", "DAO"
    AddRef wbk, "{00020905-0000-0000-C000-000000000046}", "Word"
    AddRef wbk, "{91493440-5A91-11CF-8700-00AA0060263B}", "PowerPoint"
End Sub

Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String)
    Dim i As Integer
    On Error GoTo EH
    With wbk.VBProject.References
        For i = 1 To .Count
            If .Item(i).Name = sRefName Then
               Exit For
            End If
        Next i
        If i > .Count Then
           .AddFromGuid sGuid, 0, 0 ' 0,0 should pick the latest version installed on the computer
        End If
    End With
EX: Exit Sub
EH: MsgBox "Error in 'AddRef'" & vbCrLf & vbCrLf & err.Description
    Resume EX
    Resume ' debug code
End Sub

Public Sub DebugPrintExistingRefs()
    Dim i As Integer
    With Application.ThisWorkbook.VBProject.References
        For i = 1 To .Count
            Debug.Print "    AddRef wbk, """ & .Item(i).GUID & """, """ & .Item(i).Name & """"
        Next i
    End With
End Sub

最终稍作修改:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

Private Sub AddWordReferences()
    Dim vbProj As Object: Set vbProj = ActiveWorkbook.VBProject
    With vbProj.References
        For i = 1 To .Count
            If .Item(i).Name = "Word" Then
               Exit For
            End If
        Next i
        If i > .Count Then
           .AddFromGuid "{00020905-0000-0000-C000-000000000046}", 0, 0 ' 0,0 should pick the latest version installed on the computer
        End If
    End With
    Debug.Print "-----After add reference-----"
    With vbProj.References
        For i = 1 To .Count
           Debug.Print .Item(i).Name
        Next i
    End With
End Sub