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.EventArg s) Handles miscinfo.Enter
Dim strConn, strFileFormat, strLocalFileNam e As String
Dim TableNames As DataTable
Dim objDAO As DAO
Try
Cursor.Current = Cursors.WaitCur sor
strLocalFileNam e = Me.txtPath.Text
strFileFormat = Me.lbFileFormat .Text
If strFileFormat = "Excel" Or strFileFormat = "Access" Then
If strLocalFileNam e <> String.Empty Then
Me.lbTableNames .Visible = True
If strFileFormat = "Excel" Then
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
strLocalFileNam e & ";Extended Properties=""Ex cel 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=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
strLocalFileNam e
'get the list of tables in the Access mdb file
End If
objDAO = New DAO(strConn)
objDAO.GetSchem a(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.N ame,
MessageBoxButto ns.OK, MessageBoxIcon. Information)
Finally
objDAO = Nothing
Cursor.Current = Cursors.Default
End Try
End Sub
=============== =============== =============== =============
Private Sub lbTableNames_Se lectedIndexChan ged(ByVal sender As
System.Object, ByVal e As System.EventArg s) Handles
lbTableNames.Se lectedIndexChan ged
Me.miscinfo.Tex t = CStr(Me.lbTable Names.SelectedV alue)
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(m ConnStr)
Dim cmd As New SqlCommand(strS QL, cnn)
Try
cmd.CommandType = CommandType.Tex t
cnn.Open()
da = New SqlClient.SqlDa taAdapter(cmd)
da.FillSchema(d t, SchemaType.Sour ce)
Catch exc As Exception
Finally
cnn.Close()
End Try
ElseIf DBtype = "Oracle" Then
Dim da As OracleDataAdapt er
Dim cnn As New OracleConnectio n(mConnStr)
Dim cmd As New OracleCommand(s trSQL, cnn)
Try
cmd.CommandType = CommandType.Tex t
cnn.Open()
da = New OracleClient.Or acleDataAdapter (cmd)
da.FillSchema(d t, SchemaType.Sour ce)
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.GetOleDbSch emaTable(OleDbS chemaGuid.Table s, New Object()
{Nothing, Nothing, Nothing, "TABLE"})
Catch exc As Exception
Finally
cnn.Close()
End Try
End If
End Sub
--
Joe Fallon
"Conrad F" <co*********@ho tmail.com> wrote in message
news:6a******** *************** ***@posting.goo gle.com...
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
