I can see the Excel Workbook, but the WorkSheet methods etc don't work :(

[johnhoens]

I downloaded the "Free" version of vb.net from Microsoft. Seems to work fine for "Hello World" programs.
My problem is that no matter what I do I can't access anything in my xlWorkSheet object... The xlWorkBook however
seems to be fine. I know this since I added a second worksheet and my vb.net program was able to see it.
I also tried getting vb.net to 'run' my .VBS script which is able to access and process my worksheet. In the end I can
ctreate a .BAT file to run my .VBS script to cast the .xlsx file to a .csv and have vb.net read it from there?
Why won't this work? Do I need the Professional Version?

Imports System
Imports System.IO
Imports System.Xml
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel



Module Program
Sub Main()
Console.WriteLine("Begin MyProgram Excel with VB.Net")
Dim CurrentPath As String
CurrentPath = System.IO.Path.Combine(Directory.GetCurrentDirectory())
Console.WriteLine("Path>" & currentPath)

Dim MyStr As String
Dim xlApp = New Excel.Application
Dim xlWorkBook = xlApp.Workbooks.Open("C:\Users\johnh\Desktop\ExcelFile.xls") ' WORKBOOK TO OPEN THE EXCEL FILE.
Dim xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
MyStr = xlWorkBook.Author
Console.Write("MyStr>" & MyStr & vbCrLf)
MyStr = xlWorkBook.Creator
Console.Write("MyStr>" & MyStr & vbCrLf)
MyStr = xlWorkBook.Sheets.Count
Console.Write("MyStr>" & MyStr & vbCrLf)

'MyStr = xlWorkSheet ??????
'Console.Write("MyStr>" & MyStr & vbCrLf)

xlWorkBook.Close()
xlApp.Quit()
End Sub
End Module


Here is my Console Log:

Begin MyProgram Excel with VB.Net
Path>C:\Users\johnh\Desktop\ExcelRead\ExcelRead\bin\Debug\net6.0
MyStr>John Hoens
MyStr>1480803660
MyStr>2

C:\Users\johnh\Desktop\ExcelRead\ExcelRead\bin\Debug\net6.0\ExcelRead.exe (process 1524) exited with code 0.
To automatically close the console when debugging stops, enable Tools->Options->Debugging->Automatically close the console when debugging stops.
Press any key to close this window . . .

 

[JohnH]

Worksheets("Sheet1") returns type Object, it is short for Worksheets.Item("Sheet1") using the Item property: Worksheets.Item[Object] Property (Microsoft.Office.Interop.Excel)
Cast it to type Excel.WorkSheet:

Dim xlWorkSheet = CType(xlWorkBook.Worksheets("Sheet1"), Excel.WorkSheet)

 

[johnhoens]

Worksheets("Sheet1") returns type Object, it is short for Worksheets.Item("Sheet1") using the Item property: Worksheets.Item[Object] Property (Microsoft.Office.Interop.Excel)
Cast it to type Excel.WorkSheet:

Dim xlWorkSheet = CType(xlWorkBook.Worksheets("Sheet1"), Excel.WorkSheet)

First, Thank You for reviewing my issue and giving me the code which lead me to a solution.

Here is what I wanted Rows and Cols -- like it used to work)

dim i,j as integers
dim MyArray(rows,cols)
for i = 1 to rows
for j = 1 to cols
MyArray(i,j) = xlWorkSheet.cell(i,j)
next j
next i

What I wound up with Works! With your Help, but I got off on a tangent involving the "Range" function. Perhaps you can take a peek at the result and offer a few suggestions, either way what I have is good enough for my needs.
Pay Attention to the "For Each c In xlWorkSheet.Range(MyRange)" loop.

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Module Program
Sub Main()
Console.WriteLine("Begin>MyProgram Excel with VB.Net")
Dim CurrentPath As String
CurrentPath = System.IO.Path.Combine(Directory.GetCurrentDirectory())
Console.WriteLine("Path>" & CurrentPath)

Dim MyStr As String
Dim xlApp = New Excel.Application
Dim xlWorkBook = xlApp.Workbooks.Open("C:\Users\johnh\Desktop\ExcelFile.xlsx") ' WORKBOOK TO OPEN THE EXCEL FILE.
Dim xlWorkSheet = CType(xlWorkBook.Worksheets("Sheet1"), Excel.Worksheet) 'Courtesy of JohnH

MyStr = xlWorkBook.Sheets.Count
Console.Write("WorkBook Sheets>" & MyStr & vbCrLf)
MyStr = xlWorkSheet.Name
Console.Write("WorkSheet Name->" & MyStr & vbCrLf)
MyStr = xlWorkSheet.UsedRange.Rows.Count
Console.Write("WorkSheet Rows->" & MyStr & vbCrLf)
MyStr = xlWorkSheet.UsedRange.Columns.Count
Console.Write("WorkSheet Cols->" & MyStr & vbCrLf)

Dim i, j As Integer
Dim MyArray(xlWorkSheet.UsedRange.Rows.Count, xlWorkSheet.UsedRange.Columns.Count) As String
Dim MyRange As String
MyRange = "A1:C" & xlWorkSheet.UsedRange.Rows.Count
Console.Write("WorkSheet Range>" & MyRange & vbCrLf)
i = 1
j = 0
For Each c In xlWorkSheet.Range(MyRange)
MyStr = xlWorkSheet.Range(c).Value
j = j + 1
If j > 3 Then
j = 1
i = i + 1
End If
MyArray(i, j) = MyStr
Console.Write("MyCell Value>" & MyStr & "I>" & i & " J>" & j & vbCrLf)
Next

xlWorkBook.Close()
xlApp.Quit()
Console.WriteLine("EndOf>MyProgram Excel with VB.Net")
End Sub
End Module


Console Log...

Begin>MyProgram Excel with VB.Net
Path>C:\Users\johnh\Desktop\ExcelRead\ExcelRead\bin\Debug\net6.0
WorkBook Sheets>1
WorkSheet Name->Sheet1
WorkSheet Rows->5
WorkSheet Cols->3
WorkSheet Range>A1:C5
MyCell Value>A1I>1 J>1
MyCell Value>B1I>1 J>2
MyCell Value>C1I>1 J>3
MyCell Value>A2I>2 J>1
MyCell Value>B2I>2 J>2
MyCell Value>C2I>2 J>3
MyCell Value>A3I>3 J>1
MyCell Value>B3I>3 J>2
MyCell Value>C3I>3 J>3
MyCell Value>A4I>4 J>1
MyCell Value>B4I>4 J>2
MyCell Value>C4I>4 J>3
MyCell Value>A5I>5 J>1
MyCell Value>B5I>5 J>2
MyCell Value>C5I>5 J>3
EndOf>MyProgram Excel with VB.Net

C:\Users\johnh\Desktop\ExcelRead\ExcelRead\bin\Debug\net6.0\ExcelRead.exe (process 12408) exited with code 0.
To automatically close the console when debugging stops, enable Tools->Options->Debugging->Automatically close the console when debugging stops.
Press any key to close this window . . .

 

[JohnH]

For Each c As Excel.Range In xlWorkSheet.Range(MyRange)

 

[johnhoens]

For Each c As Excel.Range In xlWorkSheet.Range(MyRange)

Once again you provided the needed inspiration and helped me solve the problem. In the end I just needed the code to reference the actual cell but I was eventually able to use the following: -- Which is now working great!
For Each c As Excel.Range In xlWorkSheet.Range(MyRange)
MyStr = c.Value

My Thanks to the Forum for helping me with this!

MyIssue.Close()