Read excel file without worksheet name

Question

Dear all,

 

I want to read an excel file using VB.NET program. I know the logic below can do this.

But I have a question. What if I don't know the name of the worksheet OR if the name of the worksheet is not SHEET1?

 

Dim DS As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _      "provider=Microsoft.Jet.OLEDB.4.0; " & _      "data source=C:\myData.XLS; " & _      "Extended Properties=Excel 8.0;") ' Select the data from Sheet1 of the workbook. MyCommand = New System.Data.OleDb.OleDbDataAdapter( _      "select * from [Sheet1$]", MyConnection) DS = New System.Data.DataSet() MyCommand.Fill(DS) MyConnection.Close()

Answer 1

Hi Baby programmer,

 

If you don't know WorkSheet name, you can firstly retrieve all WorkSheet names, then access specific WorkSheet. Please check the following code sample:

 

You need to Add Reference to Microsoft Excel Object Library.

Code Snippet

Imports System.Data.OleDb

Imports Microsoft.Office.Interop.Excel

Imports Microsoft.Office.Interop

 

Public Class Form1

 

    Dim SheetList As New ArrayList

   

    ' Part 1: Retrieve all WorkSheet names and store them into an ArrayList object

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim objExcel As Excel.Application

        Dim objWorkBook As Excel.Workbook

        Dim objWorkSheets As Excel.Worksheet

        Dim ExcelSheetName As String = ""

 

        objExcel = CreateObject("Excel.Application")

        objWorkBook = objExcel.Workbooks.Open("C:\myData.XLS")

 

        For Each objWorkSheets In objWorkBook.Worksheets

            SheetList.Add(objWorkSheets.Name)

            ' ListBox1.Items.Add(objWorkSheets.Name)

        Next

    End Sub

 

    ' Part 2: Retrieve specific WorkSheet content and display on DataGridView

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim DS As DataSet

        Dim MyCommand As OleDb.OleDbDataAdapter

        Dim MyConnection As OleDb.OleDbConnection

 

        MyConnection = New OleDb.OleDbConnection( _

             "provider=Microsoft.Jet.OLEDB.4.0; " & _

             "data source=C:\myData.XLS; " & _

             "Extended Properties=Excel 8.0;")

        ' Select the data from Sheet1 of the workbook.

        MyCommand = New OleDb.OleDbDataAdapter( _

             "select * from [" & SheetList(0) & "$]", MyConnection)

        DS = New System.Data.DataSet()

        MyCommand.Fill(DS)

        DataGridView1.DataSource = DS.Tables(0).DefaultView

        MyConnection.Close()

    End Sub

End Class

 

 

Regards,

Martin

Answer 2

Is there a way to get this to work without instantiating an excel object on the server? This causes boatload of resource issues every time that I have tried to use it.

 

Thanks,

Paul

Answer 3

paulski.mcb wrote:

Is there a way to get this to work without instantiating an excel object on the server? This causes boatload of resource issues every time that I have tried to use it.

 

Hi Paul,

 

Welcome to MSDN forums!

 

To automate Excel from .NET, you need to instantiate an Excel object like this:

 

Dim objExcel As New Excel.Application

 

or

 

Dim objExcel As Excel.Application = CreateObject("Excel.Application")

 

 

 

Here are some references:

1. http://support.microsoft.com/kb/302094

    How to automate Excel from Visual Basic .NET to fill or to obtain data in a range

 

2. http://www.c-sharpcorner.com/UploadFile/thiagu304/ExcelAutomation01052007080910AM/ExcelAutomation.aspx

    Excel Automation in .Net

 

 

Regards,

Martin

Answer 4

No. I was looking to do so WITHOUT instantiating an Excel object, via ADO.Net. And I figured it out.

 

As this should be of value to the original poster, I'll share it here.

 

Unfortunately, you can't just refer to the workbook by its ordinal reference (as we both discovered). Urgh! However, you can query your ADO.Net connection to get information about the schema of the excel workbook, which is returned as a datatable.

 

So, somthing like the following works:

 

Code Snippet

Dim myTableName = myConn.GetSchema("Tables").Rows(0)("TABLE_NAME")

 

 

 

where myConn is your ADO.Net connection to the excel spreadsheet.

 

You can the use this to retrieve the values of the worksheet:

 

Code Snippet

Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), myConn)

 

 

 

One potential gotcha: unfortunately, the worksheet names are not necessarily returned in order; this is an unordered collection, so if their are three worksheets, you would have to cycle through to find the one you need.

 

Luckily, my spreadsheet only had one single sheet (this is the way it is generated, though foolishly the online reporting app that generates the spreadsheet allows the user to specify the worksheet name; hence, my issue).

 

Since you never have to instantiate an Excel object, this should be cleaner and faster.

 

Enjoy!

Paul

 

Answer 5

Hi Paul,

 

I’m glad to hear that you have solved the problem by yourself. Cheers!

 

Thank you for sharing your good solution here. It will be very beneficial to other community members having similar questions.

 

 

Regards,

Martin

Answer 6

Thank you all for the extremely useful code. This has solved a big problem I was having with a service I am writing. Go raibh míle maith agat!

Answer 7

Hi All,

I have a similar requirement.  I am writing a code in VB6 that creates a worksheet named after an id assigned to each person. The application allows a partial save of data and the user can choose to reopen the same worksheet at a later point of time to complete filling the details, but I am having issues opening a specific worksheet.  Any help is greatly appreciated.

My logic is

create a new worksheet named after the user's id
if err.number = 1004, open the specific worksheet and start appending content to it. this is where I need some help.

My code is

' Start Excel and get Application object.
      Set oXL = CreateObject("Excel.Application")
      oXL.Visible = True
     
   ' Open a  workbook.
      Set oWB = oXL.Workbooks.Open("c:\myexcel.xls")
            
  'Iterate till the last worksheet
      With oWB.Sheets
        .Add after:=oWB.Sheets(oWB.Sheets.Count)
       End With
     
    On Error GoTo select_worksheeterr
   'Create the new worksheet at the end
    Set oSheet = oWB.ActiveSheet
    oSheet.Name = metnetid
   
select_worksheeterr:
    
    Select Case Err.Number
        Case 1004               
        
        // I would want to open the worksheet with metnet id of the user and start appending the content.  --- This is where I am stuck.
            
        Case Else
           
            'Save the workbook
            oWB.Save
            // Error handling

    End Select

Answer 8

It's likely to get quick and better responses at related VB6 forums. Thanks!

Sticky: For Visual Basic 6 questions, please read...

Answer 9

i thought i would mention also a program called spreadsheetgear for .net     works with excel files without the need to start the excel process.  all can be done in memory and has the ability to get the range of worksheets in the workbook along with the names.  very much like automating excel but not the heavy resource usage.  i use it for many things.  if you have vb express there is a free version in the registration benefits.  might be something that will help out now or later.  i have quite a few example for it on my website as well.  and there are many samples in the express forum.  worth a look. 

Jeff - www.srsoft.us

Edit  


forgot to mention it is embeddable in your vb.net apps.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us

Answer 10

I have a same requirement. in my excel file there are only one sheet and i don't want to user excel object and pass the worksheet name dynamically.

i tried your solution.
"SELECT * FROM [{0}]"

but it's showing me the error.

  The Microsoft Jet database engine could not find the object '{0}'. Make sure the object exists and that you spell its name and the path name correctly.

can you please tell me what i have to do?

my code  (it's perfactly working if i pass any sheet name.)
Thanks
-----------------

oledbConn.Open();

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [{0}]", oledbConn);

// Create new OleDbDataAdapter

OleDbDataAdapter oleda = new OleDbDataAdapter();

oleda.SelectCommand = cmd;

// Create a DataSet which will hold the data extracted from the worksheet.

DataSet ds = new DataSet();

// Fill the DataSet from the data extracted from the worksheet.

oleda.Fill(ds, "GreenSheet");

---

ManojPatel

Answer 11

This my working code in C# to read the Excel sheet name:

String

 

conString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\temp\myFile.xls;"

+

"Extended Properties=Excel 8.0;";

 

OleDbConnection myConnection = new OleDbConnection(conString);

 

try

{

myConnection.Open();

 

DataTable sheetTable = myConnection.GetSchema("Tables");

 

DataRow rowSheetName = sheetTable.Rows[0];

String sheetName = rowSheetName[2].ToString();

myConnection.Close();

}

catch (Exception ex)

{

}

Answer 12

Paul,

For those of us who have never used ADO connection objects, could you provide more of the code surrounding the snippets?  I've instantiated Excel or Word objects, but am utterly unfamiliar with ADO connections objects, let alone their use as a way to poke around in spreadsheets.  This looks great, once the bafflement clears (with your help).

Jim

Answer 13

You can do it the following way:

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcelFile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

con.Open()

Dim dt As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) 'This will give u list of all the worksheets in the excel file

Dim ds As New DataSet

For Each row As DataRow In dt.Rows                
      Dim sq As New OleDbDataAdapter("select * from [" & row.Item("TABLE_NAME") & "]", con)
      Dim tempDT As New DataTable
      sq.Fill(tempDT)
      tempDT.TableName = row.Item("TABLE_NAME")
      ds.Tables.Add(tempDT)
Next

---

Prog

Answer 14

Hi GradProd,

I don't think the Op is watching this thread anymore it is 15 months old and already marked as answered

---

Success
Cor

Answer 15

Hi,

you can use this excellent Excel .NET library for reading and writing Excel files without knowing worksheet names in advance.

Here is a sample Excel VB.NET code how to read value from first worksheet without knowing its name:

 Dim ef As New ExcelFile

' Loads Excel file.
ef.LoadXls("filename.xls")

' Selects first worksheet.
Dim ws As ExcelWorksheet = ef.Worksheets(0)

' Displays the value of first cell in the messageBox.
MessageBox.Show(ws.Cells("A1").Value.ToString())

 

Answer 16

Hi GradProd!

Unfortunately I'm working still in DotNet 1.1, so all other answers failed, but yours solved my problem. Thanks!

Answer 17

Hi, Martin.

Would it be OK to ask your advise on the similar problem?

I have a data collected and stored in a bunch of Excel worksheets, created before me and by my colleagues. So, I realized I need to create a search form to look for specific data (a value within the column of specific name) that opens only those worksheets that have matching data. The problem is that I am completely new to programming.  Do you think you could point me in the right direction and show how to create a simple form  with two textboxes (one for the name, one for the value)? I would build up on it myself. If not, any advice will be appreciated.

Thank you.

Ravil.