can write to Excel cell but cannot read

Robert Smith 20 Reputation points
2023-07-27T11:57:20.11+00:00

Using VB.NET in Visual Studio 2022, I can write to an Excel cell but I can't read from it. Even after providing the row and column numbers, it still thinks it is returning a Range.

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.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,149 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,779 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 119.7K Reputation points
    2023-07-27T17:43:40.47+00:00

    Try this:

    Dim v As Object = CType(ExcelWS.Cells(1, 1), Range).Value2
    Dim s As String = CStr(v)
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.