473,245 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

importing Excel files to datagrids

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
Nov 20 '05 #1
3 8893
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

Nov 20 '05 #2
Open the excel file.. You can foreach the Sheets collection and each sheet
will have a name property. You don't want to use COM or Office, but how
else would you know what to select from? You'll need to know a sheet name
to use a select, but since you can walk through the collection of sheets in
probably 3-5 lines of code....

HTH,

Bill
"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

Nov 20 '05 #3
On 18 Sep 2003 07:18:52 -0700, co*********@hotmail.com (Conrad F) wrote:

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.

Public Function ListExcelTablesNET() As Boolean

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

Try

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

ExcelConnection.Open()

ExcelTables =
ExcelConnection.GetOleDbSchemaTable(System.Data.Ol eDb.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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve Chatham | last post by:
I use the following code: Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged Dim sFile As String =...
0
by: Steve Chatham | last post by:
I need to export multiple datagrids (where we have a drill-through web application) into an Excel workbook. For instance, when you click on the first datagrid, you get a subset of the sql table. ...
9
by: | last post by:
I have a web page written in asp.net that has multiple datagrids on it that would need to be exported to Excel. Each of the datagrids would be a subset of what the datagrid above it was. Thus...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
28
by: kkadakia | last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to...
1
by: thadson | last post by:
Hi, I'm trying to import specific cells from MS Excel 2000 spreadsheets to MS Access 2000 tables then move the spreadsheets to a different directory. I'm very new to this and I'm having trouble...
1
by: =?Utf-8?B?Sm9obiBXYWxrZXI=?= | last post by:
Hi, I am using the code below to export a webpage to Excel. The webpage has three datagrids on it and they are all exported to Excel properly and everything looks very nice, but we would really...
3
by: scoots987 | last post by:
What do others do if you need to import excel files into SQL Server? My main problems are 1) zipcode formatting issues. If the column is a mix of zip and zip+4, I have problems retrieving all...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.