excell data

J

jasonseay

Active member
Joined
Jun 17, 2003
Messages
29
can someone give me an example of how to read data from an excell spreadsheet into a vb.net application.

Thanks
 
Code: 
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim myString as string
        
        initialize the xlapplication
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)

        xlBook = xlApp.Workbooks.Open(strPath & strFileName)

        xlSheet = xlBook.Worksheets(1)
        myString = xlSheet.Cells(1, 1)  (Row, Column)

        xlSheet = nothing
        xlBook.Close()
        xlBook = nothing
        xlApp.Quit()
        xlApp = Nothing

This should get you started.

You want to make very sure that the Xl objects get closed down completly or bad things will happen. A lot times if you stop execution with out closing the object down you will have to go into the Task Manager -> Processes, sort by process name to see if EXCEL.exe is running then end the process. I had 20 open at one time and the computer was doing all kinds of funky stuff till I shut down all the instances of the app, which will not show under applications on the task manager.

In order to get an Idea of the commands, record macros then look at the VBA code (in Excel) to reverse engineer the Excel Object Model. The commands will not be exactly the same for .net but close enough that you can experiment and figure it out.

MTS
 
You can also read data from excel in a very similar fashion to the way you read from a database. The only requirements are that
  • your spreadsheet has named range(s)
  • you use the jet provider
the connection string includes the following line
"Extended Properties=Excel 8.0;"

then you can use an sql statement like
"select * from yournamedrange" and fill a dataset or whatever.

Also you can use column names e.g if you use row 1 of the spreadsheet to name the columns you can use those names in your sql statement.
 
thanks

I Tried the

Dim aExcel as Excel.Application

aExcel = cType(CreateObject("Excel.Application"),Excel.Application)


but i got the error

"Cannot Create ActiveX component"

also tried

Dim aExcel as new Excel.application

and got the error

"Access Denied"

Both of these worked on my development machine but wouldnt work on the server.

I even went to the extreme of installing Excel on the server and still got the same errors

can anyone help me out?
 
Is there any way you can check the server to see if someone has the document open on anouther computer. That is the error I get when that condition exsists.

MTS
 
Back
Top