使用Worksheet函数从Word VBA中检索Excel上的数据

Question

我正试图通过检索各种excel表格中的数据来自动填充MS word文件中的表格。为了正确地与Excel交互，使用工作表功能将非常方便。然而，由于宏在MS Word中，我被阻止了。有办法访问它吗？干杯

Dim src As Workbook
Dim ws As Worksheet
Dim t As String
Dim c As Integer
    
t = ThisDocument.Tables(1).Cell(1, 1).Range.Text
t = Left(t, Len(t) - 1)
Set src = workbooks.Open("https://collab.ext.../asd.xlsx", True, True)
Set ws = src.Worksheets("Data")
c = worksheetfunction.Match(t, ws.Range("A1:AA1"), False)
src.Close
Set src = Nothing

Answer 1

@Tim Williams感谢您的帮助。更正了以下代码：

Dim oXL As Object
Dim oWB As Workbook
Dim oWS As Worksheet
Dim wbPath As String
Dim t As String
Dim c As Integer
Set oXL = CreateObject("Excel.Application")
oXL.Visible = False
wbPath = "https://collab.ext...asd.xlsx"
    
t = ThisDocument.Tables(1).Cell(1, 1).Range.Text
t = Left(t, Len(t) - 2)
Set oWB = oXL.workbooks.Open(wbPath, True, True)
Set oWS = oWB.Sheets("Data")
    
c = oXL.worksheetfunction.Match(t, oWS.Range("A1:AA3"), False)
oXL.ActiveWorkbook.Close SaveChanges:=False
oXL.Application.Quit
Set oXL = Nothing
Set oWB = Nothing
Set oWS = Nothing