Reading data from Excel Worksheet in vb

[imranmp]

below is the code I am using to read the column names from an excel
worksheet... its working fine except in two instances:
1) if i read from an empty worksheet then it returns F1 - how can i make it
not return anything
2) if i have multiple columns with different "types" of names then it
returns the generic (F1,F2,F3....) for example if i have 4 colums names:
"First Name", "Last Name", "1/1/2009", "City"
if these are the four coumn names(first row of the worksheet), then it
returns: "First Name", "Last Name", "F3", "City"
notice the F3 instead of the 1/1/2009 - how can i avoid this?

The user selects the excel worksheet to read from hence I dont know how many
columns are there and what kind of datatype is the column name...


Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next

 

[Joel]

You should add sometype of unique identified into the worksheet so that you
can test for this condition in the macro. Try these changes for your othe
comments

Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

if dt.rows.count > 0 then
For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next
else
Set dt = nothing
end if

 

[imranmp]

Thanks Joel.. but it doesnt seem to work...
dt.rows.count returns 1 even if the worksheet is empty

You should add sometype of unique identified into the worksheet so that you
can test for this condition in the macro. Try these changes for your othe
comments

Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

if dt.rows.count > 0 then
For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next
else
Set dt = nothing
end if

below is the code I am using to read the column names from an excel
worksheet... its working fine except in two instances:
1) if i read from an empty worksheet then it returns F1 - how can i make it
not return anything
2) if i have multiple columns with different "types" of names then it
returns the generic (F1,F2,F3....) for example if i have 4 colums names:
"First Name", "Last Name", "1/1/2009", "City"
if these are the four coumn names(first row of the worksheet), then it
returns: "First Name", "Last Name", "F3", "City"
notice the F3 instead of the 1/1/2009 - how can i avoid this?

The user selects the excel worksheet to read from hence I dont know how many
columns are there and what kind of datatype is the column name...


Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next

 

[Joel]

For another posting I found the return infomation from a Recordset. It says
there is a BOF and EOF propertiy. I don't know if it applies to your method.
try it. If nothing is returned I would think you are are the EOF.


When you open a Recordset, the current record is positioned to the first
record (if any) and the BOF and EOF properties are set to False. If there are
no records, the BOF and EOF property settings are True.

You can use the MoveFirst, MoveLast, MoveNext, and MovePrevious methods; the
Move method; and the AbsolutePosition, AbsolutePage, and Filter properties to
reposition the current record, assuming the provider supports the relevant
functionality. Forward-only Recordset objects support only the MoveNext
method. When you use the Move methods to visit each record (or enumerate the
Recordset), you can use the BOF and EOF properties to determine if you've
moved beyond the beginning or end of the Recordset.

Thanks Joel.. but it doesnt seem to work...
dt.rows.count returns 1 even if the worksheet is empty

You should add sometype of unique identified into the worksheet so that you
can test for this condition in the macro. Try these changes for your othe
comments

Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

if dt.rows.count > 0 then
For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next
else
Set dt = nothing
end if

below is the code I am using to read the column names from an excel
worksheet... its working fine except in two instances:
1) if i read from an empty worksheet then it returns F1 - how can i make it
not return anything
2) if i have multiple columns with different "types" of names then it
returns the generic (F1,F2,F3....) for example if i have 4 colums names:
"First Name", "Last Name", "1/1/2009", "City"
if these are the four coumn names(first row of the worksheet), then it
returns: "First Name", "Last Name", "F3", "City"
notice the F3 instead of the 1/1/2009 - how can i avoid this?

The user selects the excel worksheet to read from hence I dont know how many
columns are there and what kind of datatype is the column name...


Dim sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dataSource & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim conn As New OleDb.OleDbConnection(sConnectionString)
Dim columnList As New List(Of String)

da = New OleDb.OleDbDataAdapter("Select top 1 * from [" & worksheetName &
"]", conn)
da.Fill(dt)

For Each dc As DataColumn In dt.Columns
columnList.Add(dc.ColumnName)
Next