Opening a Sheet from Excel in VB.NET?

Dev Shed Forums Sponsor:

April 17th, 2003, 04:38 PM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 312

Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7

Opening a Sheet from Excel in VB.NET?

On the code below everything works great to populate a datagrid with excel sheet "Query1" but is there a way to find all the sheet names and then choose from a list rather then having a set name? If my sheet is named "Sheet1" for instance the code below will not work. What I would like to do is be able to populate a list with sheet names choose from the list then open up that sheet.

Any Ideas.

PHP Code:


		
			
 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                      "Data Source=" & stExcelFile & ";" & _

                      "Extended Properties=Excel 8.0;"

            'Excel Sheet Name as TableName

            myCommand = New OleDbDataAdapter("SELECT * FROM [Query1$]", strConn)

            myCommand.Fill(myDataset, "Query1")

            DataGrid1.DataSource = myDataset.Tables(0).DefaultView

__________________
George - www.ipdg3.com
Helping Developers and Programmers Find Resources
Forums - Contests - Tutorials - Source Code
ORB - Wireless Site - Online Gear

April 20th, 2003, 07:25 AM

epl

Contributing User

Join Date: Mar 2001

Location: Dublin

Posts: 413

Time spent in forums: 2 h 18 m 18 sec
Reputation Power: 8

There may be other ways to do it but this is what I've always used.
I haven't tested, it's just to get you started:

Code:

dim xlApp as Excel.Application, xlWs as Excel.WorkSheet
  set xlApp = new Excel.Application
  with xlApp
    with .WorkBooks.Open(stExcelFile)
      for each xlWs as .WorkSheets
        'add xlWs.name to list box
      next xlWs
      xlWs.close
    end with
    call xlApp.quit
  end with

You might want to play around with checking if there's a running instance of excel you can use instead of creating a new instance etc.

April 20th, 2003, 12:19 PM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 312

Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7

Hello epl

Thanks for the reply I will try it out. I think that is somewhat similiar to VB6 code as well isn't it? I really do appretiate your reply. This will allow me to have the user pick a WorkSheet then open it in the datagrid control.

Thanks

April 21st, 2003, 02:04 PM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 312

Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7

epl

I tried out your code in .NET and it is not quite working. You have to use the COM object right "Microsoft Excel 9.0 Oject Library". The "for each xlWs as .WorkSheets" Does not seem to be right. Any help would be great and if I can do this without COM References that would be even better.

PHP Code:


		
			
Public Sub getSheets(ByVal stExcelFile As String)



        Dim xlApp As Excel.Application

        Dim xlWs As Excel.Worksheet



        xlApp = New Excel.Application()



        With xlApp

            With .Workbooks.Open(stExcelFile)

                for each xlWs as .WorkSheets 'The "AS" is underlined in blue here you sure this is write.

                    ComboBox1.Items.Add(xlWs.Name)     'add xlWs.name to list box

                Next xlWs

                xlWs.close() 'This is underlined in blue

            End With

            Call xlApp.Quit()

        End With



    End Sub

April 21st, 2003, 02:10 PM

epl

Contributing User

Join Date: Mar 2001

Location: Dublin

Posts: 413

Time spent in forums: 2 h 18 m 18 sec
Reputation Power: 8

Not, it's definitely wrong - I'm sorry about that.

It should be IN and not AS.

I think I probably copied the as from the dim statement at the top. Like I said, I didn't test but just typed it straight in.

You're right about referencing Excel 9.0 library etc. That's what I was referring to as the way I've always done it. I'm sure you could pick up / write something to parse the sheet names from the xls file itself but i don't know how / whether it would be worth your while. Maybe try searching for details of the excel file format on msdn.

April 22nd, 2003, 11:15 AM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 312

Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7

Here is what I used and it worked like a champ. You will however have to use the COM Reference. "Microsoft Excel 9.0 Oject Library"

This example allows you to pass in a location and a file name as "stExcelFile" and the populates the ComboBox1 with Sheet Names.

PHP Code:


		
			
Public Sub getSheets(ByVal stExcelFile As String)

        Dim xlApp As Excel.Application

        Dim xlWs As Excel.Worksheet



        xlApp = New Excel.Application()



        With xlApp

            With .Workbooks.Open(stExcelFile)

                For Each xlWs In .Worksheets

                    ComboBox1.Items.Add(xlWs.Name)     'add xlWs.name to list box

                Next xlWs

            End With

            Call xlApp.Quit()

        End With



    End Sub

December 1st, 2003, 10:46 PM

PhillyQueenD

Junior Member

Join Date: Dec 2003

Location: Philadelphia, PA

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Hello,

I would like to know how are you passing the sheet name to the SQL string after you get it from the combobox

Thanks

December 3rd, 2003, 05:05 AM

bennf

Junior Member

Join Date: Dec 2003

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Hello

Yeah I'm trying to do a similar thing, opening an excel worksheet and feeding some data into it. Is there no .net way of doing this rather than using the COM components, seems a bit old fashioned!

December 3rd, 2003, 07:34 AM

iamtgo3

Contributing User

Join Date: Nov 2001

Location: USA

Posts: 312

Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7

Well one bad thing I have found since talking with a lot of .NET programmers is since VS.NET did not come with a lot of controls and reference that were used in VB6.You now have two options buy a 3rd party control that does what you want and supports .NET or use COM. I have not found any problems with using COM so far but it would be nice if they ported a lot of the stuff up to .NET. I am carious if Office 2003 will allow you to use a new version of "Microsoft Excel Oject Library"

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: