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
--
Joe Fallon
"Conrad F" <co*********@hotmail.com> wrote in message
news:6a**************************@posting.google.c om...
Hello All,
I know how to import a specific named excel sheet into a datagrid
using ADO.NET by setting up a JET connection and then SELECTing data
from the sheet. However, for a real world application, I would
typically not know what the names of the sheets are going to be before
I open the Excel file.
I am wondering if there is a SELECT statement that would get me the
list of sheet names so that I can place them in a combobox and
selecting one will load the appropriate sheet in a datagrid. *** I am
trying to do this without resorting to using COM and the Office object
model!!!! ***. I am hoping some bright, well informed person knows
if there is some undocumented SELECT statement I can use to get
information from the Excel file.
Thanks,
Conrad
