Excel Worksheet Name via ADO

[Lance Geeck]

Hi,

I need to be able to read data from an Excel spread sheet via ado.net

My problem is that the names of the worksheets are not standardized. Also,
there may be more than one worksheet in an excel file, and I need to let the
operator select the proper worksheet from a list.

What I am trying to do is read the worksheet names into a list box.

I'm new to the VB.NET world and would like an example of how to do the ADO
extraction of the worksheet names.

Thanks for any help you can offer.

 

[Joe Fallon]

I have a form that has a textbox named miscinfo which is where the user is
supposed to type the name of the sheet they want to import. When they Enter
it, this code runs to provide them with a list of sheets for Excel or a list
of Tables for Access. The results are placed in a Listbox named lbTableNames
which is right next to the textbox. The user can either type the name
directly or select the entry in the listbox.


Private Sub miscinfo_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles miscinfo.Enter
Dim strConn, strFileFormat, strLocalFileName As String
Dim TableNames As DataTable
Dim objDAO As DAO

Try
Cursor.Current = Cursors.WaitCursor
strLocalFileName = Me.txtPath.Text
strFileFormat = Me.lbFileFormat.Text

If strFileFormat = "Excel" Or strFileFormat = "Access" Then
If strLocalFileName <> String.Empty Then
Me.lbTableNames.Visible = True
If strFileFormat = "Excel" Then
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strLocalFileName & ";Extended Properties=""Excel 8.0;HDR=YES"""
'get the list of tables in the Excel file
'sheets end with $ and named ranges do not.
End If
If strFileFormat = "Access" Then
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strLocalFileName
'get the list of tables in the Access mdb file
End If

objDAO = New DAO(strConn)
objDAO.GetSchema(TableNames, strSQL, "OLEDB")

With Me.lbTableNames
.DisplayMember = "TABLE_NAME"
.ValueMember = "TABLE_NAME"
.DataSource = TableNames
.SelectedIndex = -1
End With
End If
End If

Catch ex As Exception
MessageBox.Show(ex.Message, ex.Source & " - " & ex.TargetSite.Name,
MessageBoxButtons.OK, MessageBoxIcon.Information)
Finally
objDAO = Nothing
Cursor.Current = Cursors.Default
End Try
End Sub
==========================================================

Private Sub lbTableNames_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lbTableNames.SelectedIndexChanged
Me.miscinfo.Text = CStr(Me.lbTableNames.SelectedValue)
End Sub
==========================================================

My DAO class has a GetSchema method:

Public Sub GetSchema(ByRef dt As DataTable, ByVal strSQL As String, ByVal
DBtype As String)
'retrieve structure information into a datatable using the FillSchema
method of a DataAdapter object
If DBtype = "SQL Server" Then
Dim da As SqlDataAdapter
Dim cnn As New SqlConnection(mConnStr)
Dim cmd As New SqlCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New SqlClient.SqlDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)

Catch exc As Exception

Finally
cnn.Close()
End Try
ElseIf DBtype = "Oracle" Then
Dim da As OracleDataAdapter
Dim cnn As New OracleConnection(mConnStr)
Dim cmd As New OracleCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New OracleClient.OracleDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)
Catch exc As Exception

Finally
cnn.Close()
End Try
ElseIf DBtype = "OLEDB" Then
Dim cnn As New OleDbConnection(mConnStr)
Try
cnn.Open()
'KB309488 - the Object array is for filtering the returned data
(only TABLEs are returned not Views, etc.)
dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, "TABLE"})
Catch exc As Exception

Finally
cnn.Close()
End Try
End If
End Sub

 

[Lance G]

Hi Joe,

Thanks for responding to my post.

I am having a problem with the line: Dim objDAO As DAO

I added a reference to Microsoft COM object DAO, but that didn't seem to
do it. Or does this refer to a Data Adapter Object of some type?

Also, on the strSQL, I don't see where value is setup. What should be
in this string?

Thanks for your help.
Lance

 

[Joe Fallon]

DAO is my personal Class that holds my data access code.
You do not need a reference to MS DAO.

The code was to give you the idea how to do it.
Cut and paste won't work.

You can copy the GetSchema method of my DAO object to a Module and then omit
all DAO references.

I don't recall if strSQL is a simple Select * From TableName.
Try it.

 

[Paul Clement]

¤ Hi,
¤
¤ I need to be able to read data from an Excel spread sheet via ado.net
¤
¤ My problem is that the names of the worksheets are not standardized. Also,
¤ there may be more than one worksheet in an excel file, and I need to let the
¤ operator select the proper worksheet from a list.
¤
¤ What I am trying to do is read the worksheet names into a list box.
¤
¤ I'm new to the VB.NET world and would like an example of how to do the ADO
¤ extraction of the worksheet names.
¤

Use ADO.NET:

Public Function ListExcelTablesNET() As Boolean

Dim ExcelConnection As System.Data.OleDb.OleDbConnection
Dim ExcelTables As DataTable

Try

ExcelConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()

ExcelTables =
ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing,
Nothing, Nothing, "TABLE"})

frmMain.DataGrid1.DataSource = ExcelTables

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ExcelConnection.Close()

End Try

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)