Using OLEDB with Excel

[genojoe]

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

Dim ExportCommand As New System.Data.Ole Db.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.

[Paul Clement]

Re: Using OLEDB with Excel

On Tue, 15 Aug 2006 09:11:03 -0700, genojoe <genojoe@discus sions.microsoft .comwrote:

¤ This line of code works fine if I know the name of the Sheet
¤
¤ Dim ExportCommand As New System.Data.Ole Db.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)

[genojoe]

Re: Using OLEDB with Excel

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)
>

[genojoe]

Re: Using OLEDB with Excel

Here is the code that I will use. It does work

Dim ExcelConnection As System.Data.Ole Db.OleDbConnect ion
Try
Dim dt As New DataTable
ExcelConnection = New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
Source=C:\Temp\ GL 8-31-06.xls;Extended Properties=Exce l 8.0;")
ExcelConnection .Open()
dt =
ExcelConnection .GetOleDbSchema Table(System.Da ta.OleDb.OleDbS chemaGuid.Table s,
New Object() {Nothing, Nothing, Nothing, "Table"})
Dim oRow As DataRow = dt.Rows(0)
Dim sTableName As String = oRow("TABLE_NAM E")
'Alternate Code
For Each oRow In dt.Rows
If dt.Columns.Cont ains("TABLE_NAM E") Then
Debug.Print(oRo w("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.

[Paul Clement]

Re: Using OLEDB with Excel

On Tue, 15 Aug 2006 15:48:01 -0700, genojoe <genojoe@discus sions.microsoft .comwrote:

¤ Here is the code that I will use. It does work
¤
¤ Dim ExcelConnection As System.Data.Ole Db.OleDbConnect ion
¤ Try
¤ Dim dt As New DataTable
¤ ExcelConnection = New
¤ System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
¤ Source=C:\Temp\ GL 8-31-06.xls;Extended Properties=Exce l 8.0;")
¤ ExcelConnection .Open()
¤ dt =
¤ ExcelConnection .GetOleDbSchema Table(System.Da ta.OleDb.OleDbS chemaGuid.Table s,
¤ New Object() {Nothing, Nothing, Nothing, "Table"})
¤ Dim oRow As DataRow = dt.Rows(0)
¤ Dim sTableName As String = oRow("TABLE_NAM E")
¤ 'Alternate Code
¤ For Each oRow In dt.Rows
¤ If dt.Columns.Cont ains("TABLE_NAM E") Then
¤ Debug.Print(oRo w("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)

[aaron.kempf@gmail.com]

Re: Using OLEDB with Excel

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 <genojoe@discus sions.microsoft .comwrote:
>
¤ Here is the code that I will use. It does work
¤
¤ Dim ExcelConnection As System.Data.Ole Db.OleDbConnect ion
¤ Try
¤ Dim dt As New DataTable
¤ ExcelConnection = New
¤ System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
¤ Source=C:\Temp\ GL 8-31-06.xls;Extended Properties=Exce l 8.0;")
¤ ExcelConnection .Open()
¤ dt =
¤ ExcelConnection .GetOleDbSchema Table(System.Da ta.OleDb.OleDbS chemaGuid..Tabl es,
¤ New Object() {Nothing, Nothing, Nothing, "Table"})
¤ Dim oRow As DataRow = dt.Rows(0)
¤ Dim sTableName As String = oRow("TABLE_NAM E")
¤ 'Alternate Code
¤ For Each oRow In dt.Rows
¤ If dt.Columns.Cont ains("TABLE_NAM E") Then
¤ Debug.Print(oRo w("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)