473,320 Members | 1,974 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,320 software developers and data experts.

Using OLEDB with Excel

This line of code works fine if I know the name of the Sheet

Dim ExportCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO
[Text;DATABASE=C:\Temp].[Import.csv] FROM [Test$]", ExcelConnection)

Also, I have seen code samples that can parse out the sheet names. If I
know that an Excel file contains only one sheet, is there a way to link into
that sheet without knowing its name? I would like to avoid the parsing if
that is possible.
Aug 15 '06 #1
5 4666
On Tue, 15 Aug 2006 09:11:03 -0700, genojoe <ge*****@discussions.microsoft.comwrote:

¤ This line of code works fine if I know the name of the Sheet
¤
¤ Dim ExportCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO
¤ [Text;DATABASE=C:\Temp].[Import.csv] FROM [Test$]", ExcelConnection)
¤
¤ Also, I have seen code samples that can parse out the sheet names. If I
¤ know that an Excel file contains only one sheet, is there a way to link into
¤ that sheet without knowing its name? I would like to avoid the parsing if
¤ that is possible.

No. You need the Sheet name and can't use an ordinal (sheet number), which would require one of the
methods that retrieves database schema information.
Paul
~~~~
Microsoft MVP (Visual Basic)
Aug 15 '06 #2
On May 12, you responded to a question with the following line.

Now if the Workbook only contains a single Worksheet then there is a native
..NET method that does not require COM automation or the use of DAO.

I was hoping that my question would elicit further elaboration on that
statement, I can't find the native .NET method that you reference.
"Paul Clement" wrote:
No. You need the Sheet name and can't use an ordinal (sheet number), which would require one of the
methods that retrieves database schema information.
Paul
~~~~
Microsoft MVP (Visual Basic)
Aug 15 '06 #3
Here is the code that I will use. It does work

Dim ExcelConnection As System.Data.OleDb.OleDbConnection
Try
Dim dt As New DataTable
ExcelConnection = New
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
Source=C:\Temp\GL 8-31-06.xls;Extended Properties=Excel 8.0;")
ExcelConnection.Open()
dt =
ExcelConnection.GetOleDbSchemaTable(System.Data.Ol eDb.OleDbSchemaGuid.Tables,
New Object() {Nothing, Nothing, Nothing, "Table"})
Dim oRow As DataRow = dt.Rows(0)
Dim sTableName As String = oRow("TABLE_NAME")
'Alternate Code
For Each oRow In dt.Rows
If dt.Columns.Contains("TABLE_NAME") Then
Debug.Print(oRow("TABLE_NAME"))
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ExcelConnection.Close()
End Try

Thank you

"Paul Clement" wrote:
No. You need the Sheet name and can't use an ordinal (sheet number), which would require one of the
methods that retrieves database schema information.
Aug 15 '06 #4
On Tue, 15 Aug 2006 15:48:01 -0700, genojoe <ge*****@discussions.microsoft.comwrote:

¤ Here is the code that I will use. It does work
¤
¤ Dim ExcelConnection As System.Data.OleDb.OleDbConnection
¤ Try
¤ Dim dt As New DataTable
¤ ExcelConnection = New
¤ System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
¤ Source=C:\Temp\GL 8-31-06.xls;Extended Properties=Excel 8.0;")
¤ ExcelConnection.Open()
¤ dt =
¤ ExcelConnection.GetOleDbSchemaTable(System.Data.Ol eDb.OleDbSchemaGuid.Tables,
¤ New Object() {Nothing, Nothing, Nothing, "Table"})
¤ Dim oRow As DataRow = dt.Rows(0)
¤ Dim sTableName As String = oRow("TABLE_NAME")
¤ 'Alternate Code
¤ For Each oRow In dt.Rows
¤ If dt.Columns.Contains("TABLE_NAME") Then
¤ Debug.Print(oRow("TABLE_NAME"))
¤ End If
¤ Next
¤ Catch ex As Exception
¤ MessageBox.Show(ex.Message)
¤ Finally
¤ ExcelConnection.Close()
¤ End Try
¤
¤ Thank you
That's it. You got it.
Paul
~~~~
Microsoft MVP (Visual Basic)
Aug 16 '06 #5
Excel is a disease.

I reccomend uninstalling it from every machine at every company in the
world.

-Aaron
Paul Clement wrote:
On Tue, 15 Aug 2006 15:48:01 -0700, genojoe <ge*****@discussions.microsoft.comwrote:

¤ Here is the code that I will use. It does work
¤
¤ Dim ExcelConnection As System.Data.OleDb.OleDbConnection
¤ Try
¤ Dim dt As New DataTable
¤ ExcelConnection = New
¤ System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;Data
¤ Source=C:\Temp\GL 8-31-06.xls;Extended Properties=Excel 8.0;")
¤ ExcelConnection.Open()
¤ dt =
¤ ExcelConnection.GetOleDbSchemaTable(System.Data.Ol eDb.OleDbSchemaGuid..Tables,
¤ New Object() {Nothing, Nothing, Nothing, "Table"})
¤ Dim oRow As DataRow = dt.Rows(0)
¤ Dim sTableName As String = oRow("TABLE_NAME")
¤ 'Alternate Code
¤ For Each oRow In dt.Rows
¤ If dt.Columns.Contains("TABLE_NAME") Then
¤ Debug.Print(oRow("TABLE_NAME"))
¤ End If
¤ Next
¤ Catch ex As Exception
¤ MessageBox.Show(ex.Message)
¤ Finally
¤ ExcelConnection.Close()
¤ End Try
¤
¤ Thank you
That's it. You got it.
Paul
~~~~
Microsoft MVP (Visual Basic)
Aug 17 '06 #6

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

Similar topics

2
by: Roland Hall | last post by:
I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already found one bug, although stated the bug was only in ODBC, which I'm not using. It...
1
by: Roger Twomey | last post by:
I have a form that allows a user to upload a microsoft Excel Spreadsheet. (I am testing with one created in Excel 2000) The spreadsheet is uploaded correctly. It is then supposed to be read...
1
by: funcSter | last post by:
I want to retrieve data from an Excel file like how I would with a database. I understand that I would have to use OLE DB. Somehow I think I cannot get the connection string right, as the bit of...
3
by: RJN | last post by:
Hi I've a template excel file which has all the calculations defined. There are certain input values to be entered which gives a lot of output to the user. I don't want to expose the excel sheet...
4
by: Agnes | last post by:
I searched from previous post and got that there is a solution which export the data to export without using Excel auotmation. However, my database is SQL server, How can I amend...
0
by: genojoe | last post by:
I have a 4 column, 6 row Excel sheet with all cells formatted for text: 1 1 a a 2 2 b b 3 3 c c a 4 d 1 b 5 e 2 c 6 f 3 I have the following schema.ini:
4
by: Abel | last post by:
Hi I try to execute an sample how to read and write excel sheets using OleDb When opens the connection, the objConn.Open gave me this error: "Request for the permission of type...
5
atljpe
by: atljpe | last post by:
In Excel: My query can only be refreshed if your using z: as your drive header. I want to change it so anyone can refresh this query wether they're using x, y, or z for their drive header: The...
0
by: satenova | last post by:
Hello Friends, I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works...
1
by: =?Utf-8?B?U2hlZXMgQWJpZGk=?= | last post by:
I read an article on the link: http://support.microsoft.com/default.aspx?scid=kb;en-us;306572 related to reading data from Excel using OLEDB The topic's heading is: How to query and display excel...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.