I am using the following answer to a previous question. I am using Visual Basic 2010.
Read Data from Excel using OLEDB
My problem is that I don't have control over the Excel sheet, and the column headers contain spaces and other non-alphanumeric characters. I need to be able to refer to individual columns (fields), but I need help with the syntax, OR find a way to ignore the Excel column headers and be able to refer to the fields with the generic F1, F2, etc (which is what happens if row 1 is empty).
Typical column headers as an example: "A: Address" "B: Name"
And this will fail, but I don't know the right syntax to make it work...
("select 'A: Address' from [Sheet1$]", MyConnection)
Similarly, this will fail...
("select * from [Sheet1$] where 'B: Name' like 'Fred%'", MyConnection)
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection _
" Data Source='c:\testfile.xls'; " _
"Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter _
("select * from [Sheet1$]", MyConnection)
DtSet = New System.Data.DataSet
DataGridView1.DataSource = DtSet.Tables(0)
Catch ex As Exception