excell data

[jasonseay]

can someone give me an example of how to read data from an excell spreadsheet into a vb.net application.

 

Thanks

[MTSkull]

       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

[mr relaxo]

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.

[jasonseay]

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 wouldn't 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?

[MTSkull]

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