Connecting to and reading data from excel using VB .net


IRonRepp (TechnicalUser) 27 May 08 20:55
Wgg:

It depends on whether you want to connect using ODBC, as described in your post, or by using the Excel object. If you choose ODBC, it will bring a spreadsheet into your app the same as a SQL or any other ODBC method. See http://www.connectionstrings.com/ for the connection string.

Using it as an object, however, opens every Excel object to you. This can be done in 2 ways; early or late binding.


CODE
Dim XL as Object
XL = CreateObject("Excel.Application")
XL.Visible = True 'This Opens Excel
XL.Range("A1").Value = "Hello"
XL.XL.ActiveWorkbook.SaveAs Filename:= _
Application.StartupPath & "\Documents\MyWorkbook.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
XL.Quit
''Kill the varible
XL = Nothing


CODE
''Set reference to Excelxx
Dim XL as Excel.Application = New Excel.Application
XL.Visible = True 'This Opens Excel
XL.Range("A1").Value = "Hello"
XL.ActiveWorkbook.SaveAs Filename:= _
Application.StartupPath & "\Documents\MyWorkbook.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
XL.Quit
''Kill the varible
XL = Nothing


OR USING THIS THE CODE

ZmrAbdulla (TechnicalUser) 28 May 08 5:26
What I know is something different

CODE
Imports System.Data.OleDb
CODE
Private Sub btnLoad_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoad.Click
Try

Dim MyConnection As OleDbConnection
Dim MyDataSet As DataSet
Dim MyCommand As OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Book1.xls; " _
& "Extended Properties=Excel 8.0;")

MyCommand = New System.Data.OleDb.OleDbDataAdapter _
("select * from [Sheet1$]", MyConnection)

MyDataSet = New System.Data.DataSet
MyCommand.Fill(MyDataSet)

Me.DataGridView1.DataSource = MyDataSet.Tables(0)

MyConnection.Close()


Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub



Related :
Trik wood work tool

Using radio button vb2008

Valentineday

vb2008 with control

vb2008 with control properties

vb2008 with oriented programming

VB.NET Solution files

VB.NET Code myclass

Virus protection

VisualStudio aGood Partnership

Windows explorer vb2005

Writing first vb2008

Writing the Code vb2008

Explorer in vb2008

SQLserver2005 security