I have always used Excel.interop before but I thought oledb would be simpler because, for this application, I only have to read the data which it can't seem to do. It accesses the file alright because I can't open the file while I am at a breakpoint. It attempts to read the file because oExcelReader.fieldcount = 14 which is correct. oExcelReader.hasrows = true which is not correct. The line where I use getstring(1) errors out and says there is no data for this row/column.
I have googled and googled but can't find an answer to this problem. People usually give up and use interop which I am very close to doing.
Structure VTSPointData
Dim Name As String
Dim Description As String
Dim Area As String
Dim IODevice As String
Dim Address As String
Dim Minimum As Int32
Dim maximum As Int32
Dim EGU As String
End Structure
Public Class Form1
Private sExcelConn As String
Private oExcelReader As OleDbDataReader
Private oExcelConn As OleDbConnection
Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
sExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\myfile.xls; Extended Properties=""Excel 8.0;HDR = YES"""
oExcelConn = New OleDbConnection(sExcelConn)
oExcelConn.Open()
GetTagInfo()
End Sub
Private Sub GetTagInfo()
Dim oPoint As VTSPointData
Dim sType As String 'Gets the type of block
Dim oCommand As New OleDbCommand("SELECT * FROM [BB$]", oExcelConn)
Dim oTagData As New DataSet
oExcelReader = oCommand.ExecuteReader
oExcelReader.GetValues(TempArray)
oPoint = New VTSPointData
oPoint.Name = oExcelReader.GetString(1) 'it errors out on this line. no data exist for the row/column
sType = oExcelReader.GetString(2)
oPoint.Description = oExcelReader.GetString(3) & " " & oPoint.Name
oPoint.Area = GetArea(oExcelReader.GetString(14))
oPoint.IODevice = GetDevice(oExcelReader.GetString(5))
oPoint.Address = GetAddress(oExcelReader.GetString(5))
If sType = "AI" Or sType = "AO" Or sType = "AR" Then
oPoint.maximum = oExcelReader.GetInt32(11)
oPoint.Minimum = oExcelReader.GetInt32(10)
oPoint.EGU = oExcelReader.GetString(12)
End If
End Sub
Private Function GetArea(ByRef sArea As String) As String
return "area"
End Function
Private Function GetDevice(ByRef sDevice As String) As String
return "device"
End Function
Private Function GetAddress(ByRef sAddress As String) As String
return "Address"
End Function
End Class