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