vba 如何从 Excel 调用 Word 宏

How to call Word macros from Excel

提问by Graham Perry

I have two macros, one in Excel, and one in Word. The Excel Macro calls the Word macro. My code is as follows:

我有两个宏，一个在 Excel 中，一个在 Word 中。Excel 宏调用 Word 宏。我的代码如下：

Excel:

电子表格：

Public wb1 As Workbook
Public dt1 As Document

Sub openword()
Dim wpath, epath As String      'where the word document will be opened and where the excel sheet will be saved
Dim wordapp As Object           'preparing to open word
Set wb1 = ThisWorkbook

While wb1.Sheets.Count <> 1
    wb1.Sheets(2).Delete
Wend

wpath = "C:\users\GPerry\Desktop\Projects and Work\document.docm"
Set wordapp = CreateObject("Word.Application")
'Set wordapp = CreateObject(Shell("C:\Program Files (x86)\Microsoft Office\Office14\WINWORD", vbNormalFocus)) this is one I tried to make work because while word.application seems to work, I don't *understand* it, so if anyone can help, that'd be awesome
wordapp.Visible = True
Set dt1 = wordapp.Documents.Open(wpath)
wordapp.Run "divider", wb1, dt1
dt1.Close
wordapp.Quit
End Sub

And word:

还有一句话：

Sub divider(wb1, dt1)
Set dt1 = ThisDocument
If dt1.Paragraphs.Count > 65000 Then
    Set cutrange = dt1.Range(dt1.Paragraphs(1).Range.Start, dt1.Paragraphs(65000).Range.End)
    If wb1.Sheets(Sheets.Count).Cells(1, 1) <> "" Then
        wb1.Sheets.Add After:=Sheets.Count
    End If
Else
    Set cutrange = dt1.Content
    If wb1.Sheets(Sheets.Count).Cells(1, 1) <> "" Then
        wb1.Sheets.Add After:=Sheets.Count
    End If
End If
    cutrange.Cut Destination:=wb1.Sheets(wb1.Sheets(Sheets.Count)).Cells(1, 1)
    wb1.Sheets(Sheets.Count).Cells(1, 1).TextToColumns Destination:=wb1.Sheets(1).Cells(1, 1)
End Sub

My problem is that the variable wb1 isn't getting passed between them. Even though I put wb1 in the list of variables to send to the macro, when it arrives at the document, wb1 has no value inside of it. I would re-initialize it, but I don't know how to refer to an already existing document - only how to set it equal to one as you open it.

我的问题是变量 wb1 没有在它们之间传递。即使我将 wb1 放在要发送给宏的变量列表中，但当它到达文档时， wb1 在其中没有任何值。我会重新初始化它，但我不知道如何引用一个已经存在的文档——只知道如何在打开它时将它设置为等于 1。

So either how do I pass the value through into the Word macro, or how do I re-initialize this variable? Preferably without having to set something equal to the excel application, because every time I try that it sets it equal to Excel 2003, not 2010 (though any solutions to that are also, of course, welcome).

那么如何将值传递到 Word 宏中，或者如何重新初始化此变量？最好不必设置等于 excel 应用程序的内容，因为每次我尝试将其设置为等于 Excel 2003，而不是 2010（当然，任何解决方案也是受欢迎的）。

Thanks!

谢谢！

回答by Comintern

You can't use the Excel global objects from inside of Word without explicitly qualifying them (they simply don't exist there). In particular, that means you can't use Sheets. You should also explicitly declare the variable types of your parameters - otherwise they'll be treated as Variant. This is important with reference types because in that it helps prevent run-time errors because the compiler knows that the Setkeyword is required.

您不能在没有明确限定的情况下从 Word 内部使用 Excel 全局对象（它们根本不存在于那里）。特别是，这意味着您不能使用Sheets. 您还应该明确声明参数的变量类型 - 否则它们将被视为Variant. 这对于引用类型很重要，因为它有助于防止运行时错误，因为编译器知道该Set关键字是必需的。

Sub divider(wb1 As Object, dt1 As Document)
    Set dt1 = ThisDocument
    If dt1.Paragraphs.Count > 65000 Then
        Set cutrange = dt1.Range(dt1.Paragraphs(1).Range.Start, dt1.Paragraphs(65000).Range.End)
        If wb1.Sheets(wb1.Sheets.Count).Cells(1, 1) <> "" Then
            wb1.Sheets.Add After:=wb1.Sheets.Count
        End If
    Else
        Set cutrange = dt1.Content
        If wb1.Sheets(wb1.Sheets.Count).Cells(1, 1) <> "" Then
            wb1.Sheets.Add After:=wb1.Sheets.Count
        End If
    End If
    cutrange.Cut Destination:=wb1.Sheets(wb1.Sheets(wb1.Sheets.Count)).Cells(1, 1)
    wb1.Sheets(wb1.Sheets.Count).Cells(1, 1).TextToColumns Destination:=wb1.Sheets(1).Cells(1, 1)
End Sub

Note - you also don't need to pass dt1at all. You never use the value in the parameter and actually set it to something else. This could be a source of errors if you're using internal calls, because dt1is implicitly passed ByRef(it gets boxed when you call it through Application.Run). That means whenever you call divider, whatever you pass to dt1in the calling code will change to ThisDocument. You should either remove the parameter or specify that it is ByVal.

注意 - 您也根本不需要通过dt1。您永远不会使用参数中的值，而是将其实际设置为其他值。如果您使用内部调用，这可能是错误的来源，因为它dt1是隐式传递的ByRef（通过调用它时它会被装箱Application.Run）。这意味着无论何时您调用divider，您dt1在调用代码中传递的任何内容都将更改为ThisDocument。您应该删除该参数或指定它是ByVal。

回答by ASH

Borrowed from another SO link.

从另一个 SO 链接借来的。

Sub Sample()
    Dim wdApp As Object, newDoc As Object
    Dim strFile As String

    strFile = "C:\Some\Folder\MyWordDoc.dotm"

    '~~> Establish an Word application object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

    wdApp.Visible = True

    Set newDoc = wdApp.Documents.Add(strFile)

    Call wdApp.Run("YHelloThar", "Hello")

    '
    '~~> Rest of the code
    '
End Sub