Using OLEDB with Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
¤ 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)
 
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.
 
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=Microsoft.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.OleDb.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
 
¤ 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=Microsoft.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.OleDb.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)
 
Excel is a disease.

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

-Aaron
 
Back
Top