Question can write to Excel cell in VB but cannot read from it

[rsmith1302]

I’m using VB.Net in Visual Studio 2022. In the following code, writing to the cell works. You can see the new value if you put a watch on it.

Dim ExcelApp As New Excel.Application
Dim ExcelWBs As Excel.Workbooks
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet
ExcelWBs = ExcelApp.Workbooks
ExcelWB = ExcelWBs.Add
ExcelWS = ExcelWB.Sheets.Add
ExcelWS.Cells(1, 1) = "qwerty"

However, trying to read the cell throws an exception. "ExcelWS.Cells(1, 1).Value2" gives the message "Public member 'Value2' on type 'Range' not found." I get the same exception if I try “? ExcelWS.Cells(1, 1).Value2” in immediate mode. Furthermore, autocomplete doesn’t show a Value2 member (but the watch does). And "debug.print(ExcelWS.Cells(1, 1)" gives "Conversion from type 'Range' to type 'String' is not valid."

Why don't the indices turn the Range object into a scalar on a read, while they work on a write (or in a watch)?

 

[Tehaali]

I’m using VB.Net in Visual Studio 2022. In the following code, writing to the cell works. You can see the new value if you put a watch on it.

Dim ExcelApp As New Excel.Application
Dim ExcelWBs As Excel.Workbooks
Dim ExcelWB As Excel.Workbook
Dim ExcelWS As Excel.Worksheet
ExcelWBs = ExcelApp.Workbooks
ExcelWB = ExcelWBs.Add
ExcelWS = ExcelWB.Sheets.Add
ExcelWS.Cells(1, 1) = "qwerty"

However, trying to read the cell throws an exception. "ExcelWS.Cells(1, 1).Value2" gives the message "Public member 'Value2' on type 'Range' not found." I get the same exception if I try “? ExcelWS.Cells(1, 1).Value2” in immediate mode. Furthermore, autocomplete doesn’t show a Value2 member (but the watch does). And "debug.print(ExcelWS.Cells(1, 1)" gives "Conversion from type 'Range' to type 'String' is not valid."

Why don't the indices turn the Range object into a scalar on a read, while they work on a write (or in a watch)?

Hi, do you find the solution?
I faced the same problem here

 

[jdelano]

I can read from an Excel file using this code:
I ma referencing Microsoft Excel 16.0 COM Object

        Dim xlApp As New Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWS As Excel.Worksheet
        Dim xlCell As Excel.Range

        Dim readValue As String

        xlWB = xlApp.Workbooks.Open("C:\Users\jd310\Documents\Book1.xlsm")
        xlWS = xlWB.Worksheets("Sheet3")
        xlCell = xlWS.Cells(1, 1)

        readValue = xlCell.Value

        MsgBox($"Value read from Excel is {readValue}")

        xlWB.Close()
        xlApp.Quit()

 

[douglasfukunaga]

Dim xlApp As New Excel.Application Dim xlWB As Excel.Workbook Dim xlWS As Excel.Worksheet Dim xlCell As Excel.Range Dim readValue As String xlWB = xlApp.Workbooks.Open("C:\Users\jd310\Documents\Book1.xlsm") xlWS = xlWB.Worksheets("Sheet3") xlCell = xlWS.Cells(1, 1) readValue = xlCell.Value MsgBox($"Value read from Excel is {readValue}") xlWB.Close() xlApp.Quit()

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim xlApp As New Excel.Application ' ... ERROR IS HERE.
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlCell As Excel.Range


ERROR = "System.InvalidCastException: 'Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. "
CAN YOU HELP? Reference was selected 'Interop.Microsoft.Office.Interop.Excel'

 

[jdelano]

Have you added a reference to Excel? Right click the project, click Add, Click COM Refence Select Microsoft Excel Object Library.

 

[douglasfukunaga]

Have you added a reference to Excel? Right click the project, click Add, Click COM Refence Select Microsoft Excel Object Library.

Thanks jDlano for the answer. Initially the Object Library was already selectedInitially .