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

rsmith1302

New member
Joined
Jul 25, 2023
Messages
4
Programming Experience
10+
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)?
 
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
 
I can read from an Excel file using this code:
I ma referencing Microsoft Excel 16.0 COM Object

Read from Excel:
        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()
Screenshot 2024-03-15 080620.png
 
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'
 
Have you added a reference to Excel? Right click the project, click Add, Click COM Refence Select Microsoft Excel Object Library.
 
Back
Top