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.

On Tue, 15 Aug 2006 09:11:03 -0700, genojoe <geno***@discussions.microsoft.com> wrote:

¤ 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.


Show quoteHide quote

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

Show quoteHide quote

On Tue, 15 Aug 2006 15:48:01 -0700, genojoe <geno***@discussions.microsoft.com> wrote:

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

IndexOf string method
CurrentUser
Ctype
vb 2005 express > querry table in access and put contents into array
Fill up string array with random characters?
Dim DBh as new DBhandler vb.net 2.0 question
How to read large text file ?
Accessing shared member - need understanding - pretty please :-)
Edit and Continue
team foundataion server