none
How can I read name of the Active Sheet of XLS file ? RRS feed

  • Question

  • I want to read data from Excel using the JET OLEDB provider.

    My Excel file has some (more than one) worksheets.

    In select command I need name of active sheet. Active sheet - sheet that active when I open XLS file.

    How can I read name of the Active Sheet of XLS file ?

     

    Here my fragment of code:

     

    string strConn = string.Format(

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=0;'",

    strFileName);

    OleDbConnection conn = new OleDbConnection(strConn);

    OleDbCommand cmd = new OleDbCommand();

    cmd.Connection = conn;

     

    string strSheetName =  // how can I read Name of active sheet ?

     

    cmd.CommandText = string.Format("Select * From [{0}$]",  strSheetName  );

    OleDbDataReader dReader = cmd.ExecuteReader();

    ...

     

     

     

    Tuesday, July 29, 2008 3:28 AM

Answers

  • Hi Andy,

     

    I am moving this thread to ADO.NET forum for you are using ADO.NET to retrieve the data in Excel file. Your issue is not related to VSTO. Experts there should give you a quick idea about this.

     

    I found the following article in codeproject http://www.codeproject.com/KB/aspnet/getsheetnames.aspx. It use some codes to retrieve all the worksheets name, but not the ActiveSheet name.

    I think if you want to get ActiveSheet name, you have to use object model to open the workbook and get the activesheet name before using ADO.NET to retrieve the data. See Cindy's link about how to use object model to automate Excel.

     

     

    Thanks,

    Ji

     

    Friday, August 1, 2008 2:48 AM
    Moderator

All replies

  • Your question is off-topic in the VSTO forum. The Please Read First message at the top of the forum explains the topics the forum targets, gives a brief overview of what VSTO is, and provides links to appropriate venues for asking non-VSTO, Office-related questions.

     

    FWIW you need to automate the Excel application object if you want to query the ActiveSheet. This KB article gives the basics of automating Office applications using the .NET Framework. It also contains an extensive list of links to articles with sample code. I recommend you start with the one about 302084 (http://support.microsoft.com/kb/302084/) How to automate Microsoft Excel from Microsoft Visual C# .NET

     

    Tuesday, July 29, 2008 9:23 AM
  • Hi Andy,

     

    I am moving this thread to ADO.NET forum for you are using ADO.NET to retrieve the data in Excel file. Your issue is not related to VSTO. Experts there should give you a quick idea about this.

     

    I found the following article in codeproject http://www.codeproject.com/KB/aspnet/getsheetnames.aspx. It use some codes to retrieve all the worksheets name, but not the ActiveSheet name.

    I think if you want to get ActiveSheet name, you have to use object model to open the workbook and get the activesheet name before using ADO.NET to retrieve the data. See Cindy's link about how to use object model to automate Excel.

     

     

    Thanks,

    Ji

     

    Friday, August 1, 2008 2:48 AM
    Moderator