0

I am trying to transfer some data from excel to an array in VB so that it can be used later on in my code. I have imported the office interop and defined what excel woorkbook to open and the correct sheet to activate. However once I rune through the loop I get the error "System.InvalidCastException: 'Conversion from type 'Range' to type 'Double' is not valid.'"

Is anyone familiar with this and point me in the right drection over what the error is?

    Dim OSpointsX(8) As Double
    Dim OSpointsY(8) As Double

    Dim MyExcel As New Excel.Application
    MyExcel.Workbooks.Open("...\Dims Update sheet 3D.xlsx")

    'extract
    MyExcel.Sheets("Sheet2").activate()

    For i = 0 To 8
        OSpointsX(i) = MyExcel.Cells(i + 3, 1)
        OSpointsY(i) = MyExcel.Cells(i + 3, 2)
    Next i
Andy P
  • 1
  • 1

1 Answers1

0

In VB.NET, default properties have to have parameters. Value, being a parameterless default property of Range, does not qualify like it did in VBA.

Specify Value explicitly:

OSpointsX(i) = MyExcel.Cells(i + 3, 1).Value
OSpointsY(i) = MyExcel.Cells(i + 3, 2).Value
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Looking through all of the complex problems and trying commands such as CType was tearing my hair out..... Who know it was so simple.... – Andy P Aug 20 '19 at 11:53
  • A two items for your consideration. 1) [Range.Value](https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Value) is not parameterless. 2)The default property on `Range` is the property `_Default([rowIndex],[colIndex])` (hidden in VBA, use show hidden properties in the Object Explorer). To use it in VB.Net to return the value, omit the index properties. i.e. `MyExcel.Cells(i + 3, 1)()`. If you supply the indices in VB.Net, it will return a `Range` object`. Please note that I do not advocate using the default property. – TnTinMn Aug 20 '19 at 16:23
  • @TnTinMn Yes, [you are correct](https://stackoverflow.com/a/32997154/11683). I might have oversimplified it. – GSerg Aug 20 '19 at 16:44