none
get the name of first worksheet of excel RRS feed

  • Question

  •  

    hi,

    i am making an application in which i have to read the data present on first worksheet of excel.

    i can not use excel object in my application because it is throwing some sort of error.

    so i am using microsoft jet to read the files.

    my problem is i am not able to get the name of first worksheet in excel

    i am using a table to fetch all the worksheet names.but by default that table sorts the worksheet names in ascending order.

    but if the user changes sheet name to XYZ,it will consider sheet2 as the first sheet which i dont want.

     

    the code for the same is:

     

    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + postedfile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

     

    Dim strSQl As String

    Dim dtSchema As System.data.DataTable

    rowString = ""

    Dim intk As Integer

    Try

    conn = New OleDbConnection(strConn)

    conn.Open()

    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

     

    If dtSchema.Rows.Count > 0 Then

    strSQl = String.Format("SELECT * FROM [{0}$]", dtSchema.Rows(0)("TABLE_NAME").ToString.Replace("'", "").Replace("$", ""))

    End If

    cmd = New OleDbCommand(strSQl, conn)

    cmd.CommandType = CommandType.Text

    da = New OleDbDataAdapter(cmd)

    Dim dt As New System.Data.DataTable

    da.Fill(dt)

     

     

    please help as i need this very urgently..........

    Wednesday, February 13, 2008 12:39 PM

Answers

  • There are only two ways, of which I am aware, that will enable you to retrieve an Excel Worksheet by
    its ordinal position. First method is to use DAO:


    Code Snippet

    Dim xlWB As DAO.Database
    Dim strFirstSheetName As String

    xlWB = OpenDatabase("C:\Test Files\Book10.xls", False, True, "Excel 8.0;")

    strFirstSheetName = xlWB.TableDefs(0).Name

    xlWB.Close

     

     


    ...and the other is to use automation with Microsoft Excel:


    Code Snippet

    Dim obj As Excel.Application
    Dim objWB As Excel.Workbook
    Dim strFirstSheetName As String

    obj = CreateObject("Excel.Application")
    objWB = obj.Workbooks.Open("C:\Test Files\Book10.xls")

    strFirstSheetName = objWB.Worksheets(1).Name

    objWB.Close
    objWB = Nothing
    obj.Quit
    obj = Nothing

     

     

    Sunday, March 2, 2008 8:57 PM

All replies

  • I'm afraid your question is off-topic for the VSTO forum. VSTO is an extension of the UI for various Office 2003 and 2007 applications. ADO.NET questions and application-specific questions, such as yours, aren't covered here. You can try asking this in the ADO.NET forum

     

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

     

    Another possibility would be the Excel newsgroup. The Excel specialists might know how you can get at the information without having to automate the file.

     

    http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=US

     

    Wednesday, February 13, 2008 7:53 PM
  • Hi there,

     

    did you ever get any solution to this problem?

     

    I need to read an excel file and process the information in the sheets keeping the same order as they are ordered in the workbook.

     

    I've found posts about getting the order of the columns, but not or the Sheets / Tables...

     

    Please inform if you had more luck than me.

     

    Cheers,

    .a

     

    Saturday, March 1, 2008 10:31 AM
  • There are only two ways, of which I am aware, that will enable you to retrieve an Excel Worksheet by
    its ordinal position. First method is to use DAO:


    Code Snippet

    Dim xlWB As DAO.Database
    Dim strFirstSheetName As String

    xlWB = OpenDatabase("C:\Test Files\Book10.xls", False, True, "Excel 8.0;")

    strFirstSheetName = xlWB.TableDefs(0).Name

    xlWB.Close

     

     


    ...and the other is to use automation with Microsoft Excel:


    Code Snippet

    Dim obj As Excel.Application
    Dim objWB As Excel.Workbook
    Dim strFirstSheetName As String

    obj = CreateObject("Excel.Application")
    objWB = obj.Workbooks.Open("C:\Test Files\Book10.xls")

    strFirstSheetName = objWB.Worksheets(1).Name

    objWB.Close
    objWB = Nothing
    obj.Quit
    obj = Nothing

     

     

    Sunday, March 2, 2008 8:57 PM