How to read .xls file using vb

[Comintern]

Hi all,

Is it possible to read an .xls file using vb unlike .txt file.

What i want to do is .....
i want to read the data of a cell of excel sheet and want to display it in a text box...... Is is possible?

Couple ways to do this. First, you can use the Excel automation objects to open the workbook and retrieve the value. The example uses early binding, but can be changed to late binding. This requires a reference to Excel:

Dim oXL As Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet, vValue As Variant
 
Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Open("D:\Test.xls")
Set oSheet = oBook.Worksheets("Sheet1")
 
vValue = oSheet.Cells(1, 1).Value       'Get the value from cell A1
Debug.Print vValue
 
Set oSheet = Nothing
oBook.Close
Set oBook = Nothing
oXL.Quit
Set oXL = Nothing

Another way would be to use ADO to open the workbook into a recordset. This does not require a reference to Excel, but I'm not exactly sure if you need Excel installed on the machine to use Excel as a data provider. I don't have any machines without Excel to test this on. Maybe someone else could shed some light on this.

Dim oRS As ADODB.Recordset, oConn As ADODB.Connection, sConString As String, sXLFile As String, vValue As Variant
 
sXLFile = "D:\Test.xls"
sConString = "Provider= Microsoft.Jet.OLEDB.4.0;" & " Data Source=" & sXLFile & ";Extended Properties=Excel 8.0;"
Set oConn = New ADODB.Connection
 
With oConn
    .CursorLocation = adUseClient
    .Open sConString
End With
 
Set oRS = New ADODB.Recordset
 
With oRS
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Source = "SELECT * FROM [Sheet1$]"
    .ActiveConnection = oConn
    .Open
    .MoveFirst
End With
 
'Work with the RS
 
oRS.Close
oConn.Close
 
Set oRS = Nothing
Set oConn = Nothing

You can read more about this method here.