Excel - referenced values via OleDB from .Net client
- by ho
I'm trying to read an Excel file (.xls, I think Excel 2003 compatible) via OleDB, but it fails to get the values for referenced fields.
This is my current test code (please note, this is just part of the class):
Private m_conn As OleDbConnection
Public Sub New(ByVal fileName As String)
Dim connString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", fileName)
m_conn = New OleDbConnection(connString)
m_conn.Open()
End Sub
Public Sub GetSheet(ByVal sheet As String)
Dim query As String = String.Format("SELECT * FROM [{0}]", sheet)
Using cmd As OleDbCommand = m_conn.CreateCommand()
cmd.CommandText = query
Dim ds As New DataSet()
Dim a As New OleDbDataAdapter(cmd)
Using rdr As OleDbDataReader = cmd.ExecuteReader()
While rdr.Read()
Debug.WriteLine(rdr.Item(0).ToString())
End While
End Using
End Using
End Sub
But if the value is a reference (something like =+'MySheetName'!K37), I just get a DBNull from the call to rdr.Item(0).
I can get around this by automating Excel instead, but would prefer not to have to use Excel automation so wondering if anyone knows how to do it.
