Retrieving values from existing worksheet

[TheVillagesBill]

I am trying to convert a program that worked well in Visual Basic 6 to the
..net version of Visual Basic. I am attempting to get a value from a cell in
an existing worksheet, but I am unable to do so. I get an error, "Exception
from HRESULT: 0x800A03EC", every way that I have tried. It fails attempting
to set temp2. The code is simple and the answer must be too, but it escapes
me. Here is the code:

Imports Microsoft.Office.Interop.Excel
Imports System.IO
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim returnFile As String = "C:\MyPool\MyPoolData\Test.xls"
Dim curFile As String = "c:\MyPool\MyPoolData\OBGSchedule.xls"
Dim temp, temp2 As String

Dim xla As New Application

Dim xlb As Workbook

Dim xls As New Worksheet
Dim xlsData As New Worksheet

xlb = xla.Workbooks.Add
xls = CType(xlb.Worksheets.Add, Worksheet)
xlsData = CType(xlb.Worksheets.Add, Worksheet)
'Dim xlr As Range

xls.Activate()
xlsData.Activate()

xlb = xla.Workbooks.Open(curFile)
xls = CType(xlb.Worksheets("Sheet1"), Worksheet)
xlsData = CType(xlb.Worksheets("Data"), Worksheet)

For Each ws As Worksheet In xlb.Worksheets
temp = ws.Name
temp2 = (ws.Range(ws.Cells(6, 2)).Value).ToString
Next

For Each ws As Worksheet In xlb.Sheets
temp = ws.Name
temp2 = (ws.Range(ws.Cells(6, 2)).Value).ToString
Next

'xlb.SaveAs(returnFile, FileFormat:=XlFileFormat.xlWorkbookNormal)
xlb.Close(SaveChanges:=False)
xla.Quit()

End Sub
End Class

 

[Tim Williams]

Maybe there's something here which might help:
http://support.microsoft.com/kb/302094

I'm not clear on why you assign "xlb" and "xlData" twice, but don't use the
first assignment ?

Tim

 

[TheVillagesBill]

I modified the code as follows and I don't get an exception, but the value
assigned to temp 2 is "System.__ComObject" as a string. Code follows:


Imports Microsoft.Office.Interop.Excel
Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim curFile As String = "c:\MyPool\MyPoolData\OBGSchedule.xls"
Dim temp, temp2 As String

Dim xla As New Application
'Dim xlb As Workbook
'Dim xls As New Worksheet
Dim xlb = xla.Workbooks.Add
xlb.Activate()
For Each ws As Worksheet In xlb.Worksheets
ws.Activate()
temp = ws.Name
temp2 = ws.Cells(6, 2).ToString
Next

xlb.Close(SaveChanges:=False)
xla.Quit()


End Sub
End Class

 

[Tim Williams]

Try:

temp2 = ws.Cells(6, 2).Value.ToString

In VBA you can get away by relying on default properties like Value: not
sure if that's also the case in VB.NET


Tim

 

[TheVillagesBill]

Nope, I tried that, but .value is not included in the intellisense selection
window. It won't compile.

 

[Tim Williams]

All of the MS examples I've seen use the .Value property, so it's a bit
strange your code won't even compile with it.

Tim

 

[TheVillagesBill]

Found the reason it didn't work. I needed to specify both ends of the range.