Opening existing excel file in VB.net 2005

[RinoStoof]

Hi there

I'm fairly new to .net and learning as I go using books and the net. I have some 4 years of VB6 behind me.

I'm writing an app in which I need to import data from a spread sheet.

I have so far wasted several hours trying to work out how to simply open the file.

In the msdn help files I found the code below, problem is it doesn't work..:
Dim wb As Excel.Workbook = _
Application.Workbooks.Open("C:\YourPath\YourWorkbo ok.xls")

I have added excel to my COM references.

I would be very grateful if someone could give me a bit of code to do this very basic thing.

Thanks very much in advance!!
Rino

[aliasruel]

Hi Rino,

This program will not only open an excel file but it will also read the content of the file.. the sample program i created for you will read data in your excel file and insert it into a dataset.. no need to add COM or any references.


'first specify the path and filename of your excel
Dim PathFilename As String = "C:\TESTME.xls"
'second is specify the sheet name you want to read
Dim strSheetName As String = "Sheet1"

'create a connection
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DS As System.Data.DataSet
Dim _sqlqry As String
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Micros oft.Jet.OLEDB.4.0; " & _
"data source='" & PathFilename & " '; " & "Extended Properties=Excel 8.0;")
'Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & strSheetName & "$]", MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
MyConnection.Close()
MyConnection.Dispose()
MyConnection = Nothing

================================================== ===

Hi there

I'm fairly new to .net and learning as I go using books and the net. I have some 4 years of VB6 behind me.

I'm writing an app in which I need to import data from a spread sheet.

I have so far wasted several hours trying to work out how to simply open the file.

In the msdn help files I found the code below, problem is it doesn't work..:
Dim wb As Excel.Workbook = _
Application.Workbooks.Open("C:\YourPath\YourWorkbo ok.xls")

I have added excel to my COM references.

I would be very grateful if someone could give me a bit of code to do this very basic thing.

Thanks very much in advance!!
Rino

[RinoStoof]

Hi aliasruel

That works an absolute treat! Thank you so much, you've made my day and saved both my head and the wall :-)

Kind regards,
Rino :-)

[aliasruel]

No problelm...

Best Regards
Ruel
[EMAIL REMOVED]

Hi aliasruel

That works an absolute treat! Thank you so much, you've made my day and saved both my head and the wall :-)

Kind regards,
Rino :-)

[RinoStoof]

Hi Ruel

I have been having a few problems with the above project..

I'm importing info for tires from the spreadsheet.

The main categories and rim sizes are put in a line by themselves in large print.

To get the categories (passenger, truck, tractor etc) I have to add in a formula to make it a row in the dataset. for obvious reasons the dataset ignores the large headers (o further column info?!). Unfortunately I need them.
The same with the rim diameter.

For some other reason the 'tread' column just doesn't show so I have to copy that into another column which it then does pick up ??

I can't really expect our customers to do this manually before importing.
I could do it for them but of course I have to get into the sheet to do so..

In VB6 I used to just go into the sheet and do whatever I wanted. Not as elegant as your method but at least I could make it do what I wanted.

So all in all I would really like to be able to open and manually manipulate the sheet.
As described above I didn't have much luck with that..

I'm quite happy to attach a copy of the sheet, just not sure this second how to do so..

Can you, or anyone else, help please?

Thanks very much in advance!

[Theone84]

hi i know code for update excel file which is given below
Imports System.Data
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String

MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"'c:\testfile.xls';Extended Properties=Excel 8.0;")

MyConnection.Open()
myCommand.Connection = MyConnection
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Updated ")
End Sub
End Class



now do any know if the cell S1 has no column name then how can i update it?