代码之家 › 专栏 › 技术社区 › D_Bester

使用Application.Intersect为什么我会得到类型不匹配

excel-interop excel vb.net

0

D_Bester · 技术社区 · 13 年前

这是我的密码。我认为这应该奏效;我正在保存wb,并试图使用wb.Application获取Excel应用程序对象。它确实有效,但XLApp.Intersect失败,并出现“类型不匹配”错误。

Dim wb As Object

Sub First()
    Dim XLApp As Object
    XLApp = CreateObject("Excel.Application")
    wb = myXL.XLApp.Workbooks.Add()
End Sub

Sub Second()
    Dim XLApp as object = wb.Application
    Dim rg as object = XLApp.Intersect(ws.UsedRange, ws.Columns("B"))
End Sub

是否可以从工作簿中获取Excel应用程序并仍然使用Intersect方法?

我还尝试将XLapp保存到一个全局变量中并重新使用它,但这也不起作用;同样的错误。

我试图避免使用Excel Interop引用来保持与旧版本Excel的向后兼容性。

MSDN Library _Application.Intersect Method

我注意到我的应用程序对象的类型是:Microsoft.Office.Interop.Excel.ApplicationClass

2 回复 | 直到 13 年前

1

0

D_Bester 13 年前

varocarbas的回答让我开始思考我使用的各种类型。所以我试验了各种各样的物体组合 Dim ObjRange1 As Object 和声明的类型 Dim rg1 As Excel.Range

这是我测试过的代码,除了最后一个给我“类型不匹配”错误外,它运行得很好。

Sub testXL1()

    Dim XLApp As Object
    Dim wb As Object
    Dim ws As Object

    XLApp = CreateObject("Excel.Application")
    XLApp.visible = True
    wb = XLApp.Workbooks.Add()
    XLApp = wb.application
    ws = wb.worksheets(1)
    ws.cells(1, 1) = "First"
    ws.cells(2, 2) = "hello"

    'this works fine
    Dim rg1 As Microsoft.Office.Interop.Excel.Range
    Dim rg2 As Microsoft.Office.Interop.Excel.Range
    Dim rg3 As Microsoft.Office.Interop.Excel.Range
    rg1 = ws.usedRange
    rg2 = ws.columns("B")
    rg3 = XLApp.Intersect(rg1, rg2)
    rg3.Select()

    'this works fine
    Dim rg4 As Object
    rg4 = XLApp.Intersect(rg1, rg2) 'Public member 'Intersect' on type 'Object()' not found.
    rg4.Select()

    'this works fine
    Dim ObjRange1 As Object = ws.usedRange
    Dim ObjRange2 As Object = ws.columns("B")
    Dim rg5 As Microsoft.Office.Interop.Excel.Range
    rg5 = XLApp.Intersect(ObjRange1, ObjRange2)
    rg5.Select()

    'this works fine
    Dim ObjRange3 As Object
    ObjRange3 = XLApp.Intersect(ObjRange1, ObjRange2)
    ObjRange3.Select()

    'this give a Type Mismatch error
    ObjRange3 = XLApp.Intersect(ws.usedRange, ws.columns("B"))
    ObjRange3.Select()


End Sub

所以我想答案是在将对象变量用作中的参数之前,将其范围设置为 ObjRange3 = XLApp.Intersect(ObjRange1, ObjRange2)

varocarbas的答案与使用DirectCast有关

rg = DirectCast(XLApp, Microsoft.Office.Interop.Excel.Application).Intersect(...

Understanding the Excel Object Model from a .NET Developer's Perspective

2

0

Diego Montania 5 年前

在我的例子中,使用c#,我需要设置行和列来获得交集。

我使用这两个范围:

using Excel = Microsoft.Office.Interop.Excel;

//open excel
object _misValue = Missing.Value;
Excel.Application myExcelApp = new Excel.Application();
Excel.Workbook myWorkbook = myExcelApp.Workbooks.Open("path/my-fileName.xlsx");

//get current sheet by position the first one
myWorksheet = myExcelApp.ActiveWorkbook.Worksheets.get_Item(1);

//First range : 5:5 the entire row
Excel.Range rangeRow;
rangeRow = myWorksheet.Range["5:5"]; 

//Second range: C:C the entire column
Excel.Range rangeColumn;
rangeColumn = myWorksheet.Range["C:C"];

//do the intersection
var result = myExcelApp.Intersect(rangeRow, rangeColumn
    , _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue
    , _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue
    , _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue).Value;

Console.WriteLine(result);

这些范围工作起来没有问题。