使用 Word VBA 自动化 Excel，我收到运行时错误“13":使用 .Find 函数时类型不匹配 [英] Using Word VBA to automate Excel, I get Run-time error '13': Type mismatch when using the .Find function

问题描述

我正在尝试将数据从 Excel 工作表获取到 Word 文档.我尝试使用查找"功能，但是在这一行上我一直收到相同的错误类型不匹配":

I'm trying to get data from an excel sheet to a word document. I try using the 'Find' function however I keep getting the same error "Type mismatch" on this line:

Set FoundRange = .Cells.Find(260707)

这是我正在运行的子程序.

Here is the subroutine I am running.

    Sub GetID()
Dim oXL As Object
Dim oWB As Object
Dim oSheet As Object
Dim WorkbookToWorkOn As String
Dim FoundRange As Range
Dim dummyvar As String

'Start a new instance of Excel
Set oXL = CreateObject("Excel.Application")
'Line to make Excel Visible or not
oXL.Visible = False
'Open the workbook
'Set the file path to access the 'Certified Personnel' table
WorkbookToWorkOn = "\\DataSource\CertifiedPersonnel.xlsx"
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oSheet = oXL.ActiveWorkbook.Sheets("tblCertifiedPersonnel")
'End of Excel Automation. Everything from this point on can reference Excel.

With oSheet
    dummyvar = .Cells(1, 2).Text
    .Cells(1, 2).Select
    'Set the range of the cell containing the ID number
     'If the ID was found
    Set FoundRange = .Cells.Find(260707)

    If Not FoundRange Is Nothing Then
        'Set the NTlogin equal to the value of column 1, and row corresponding to the FoundRange row
        NTlogin = .Cells(FoundRange.Rows, 1).Text
        Role = .Cells(FoundRange.Rows, 4).Text
    End If
End With

'End Excel reference
oXL.ActiveWorkbook.Close SaveChanges:=False
oXL.Application.Quit
Set oXL = Nothing
Set oWB = Nothing
Set oSheet = Nothing


End Sub

我知道它正在访问正确的工作簿，因为虚拟变量 (dummyvar) 正在返回我期望的值.我已经尝试了一些与查找"功能相关的事情，但是我一直无法让它工作.有任何想法吗?非常感谢.

I know it is accessing the correct workbook, because the dummy variable (dummyvar) is returning the value I expect. I have tried several things related to the 'Find' function, however I have not been able to get it to work. Any ideas? Much appreciated.

推荐答案

您正在使用后期绑定并且将 FoundRange 声明为 Range.由于这是在 Word 文档中，因此您在此处将其隐式声明为 Word.Range:

You are using late binding and have FoundRange declared as a Range. Since this is in a Word document, you're implicitly declaring it as a Word.Range here:

Dim FoundRange As Range

.Find 返回一个 Excel.Range.改为:

Dim FoundRange As Object

这篇关于使用 Word VBA 自动化 Excel，我收到运行时错误“13":使用 .Find 函数时类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！