Reading Data from Excel in VB

Make User DSN (say MyDsn) with xls file and insert DataGrid and ADODC in your form Set DataSource for DataGrid as Adodc1
In ADODC1 custom properties
Refer to MyDsn and write query as
Select * from "Sheet1$"
now if you run the program you can find Datagrid with Data from Excel Sheet

Here is some code which you may be able to use for an example, I used this in a program of mine.
Just change the filename, sheetname accordingly.
The XlsRS.Fields refers to the heading in each column, so they will need to be changed accordingly also.
Good luck, hope this helps!

Dim myFileName As String
Dim XlsDB As Database
Dim XlsRS As Recordset
Dim filepath As String
Dim sheetname As String
 
 filepath = "test.xls"
 sheetname = "Sheet1$"
 Set XlsDB = OpenDatabase(filepath, False, False, "Excel 8.0;HDR=yes;")
 Set XlsRS = XlsDB.OpenRecordset(sheetname)

 XlsRS.MoveFirst
 Screen.MousePointer = 11
 While ((XlsRS.Fields("first name") <> "")
   
    txtMemID.Text = "?"
    txtGiven = XlsRS.Fields("first name")
    txtSurname.Text = XlsRS.Fields("SURNAME")
    txtHomePhone.Text = " "
   
    If XlsRS.Fields("mphone") <> "" Then
        txtMobile.Text = XlsRS.Fields("mphone")
    Else
        txtMobile.Text = " "
    End If
   
    If XlsRS.Fields("date of birth") <> "" Then
       txtDOB.Text = XlsRS.Fields("date of birth")
    Else
       txtDOB.Text = "00/00/0000"
    End If
   
    txtEmail.Text = XlsRS.Fields("email")
    txtStreet.Text = XlsRS.Fields("Street Details")
   
    XlsRS.MoveNext
 Wend

 Screen.MousePointer = 0
 XlsDB.Close

put a mshflexgrid, 1 combo box,1 command button, add references to the ado objects and excel objects and 1 text box, 1 common dialog control

Private Sub Command1_Click()
CommonDialog1.Filter = "Excel Documents (*.xls)|*.xls" '|All files (*.*)|*.*"
CommonDialog1.DialogTitle = "RAVI"
On Error Resume Next
CommonDialog1.ShowOpen

On Error Resume Next
CommonDialog1.CancelError = True

If Err.Number = cdlCancel Then
Err.Clear
End If

If CommonDialog1.FileName <> "" Then
Text1.Text = CommonDialog1.FileName & ";"
Txt1 = CommonDialog1.FileName
End If

If CommonDialog1.FileName <> "" Then
Set WB = XL.Workbooks.Open(Txt1)

Combo1.Clear

For Each SH In WB.Worksheets
    Combo1.AddItem SH.Name '& "$"
Next
End If

If Text1.Text <> "" Then
Combo1.SetFocus
End If

Set XL = Nothing
Set WB = Nothing
Set SH = Nothing

End Sub

Private Sub Combo1_lostfocus()
If Combo1.Text <> "" Then
MSHFlexGrid1.Clear
'On Error Resume Next

objc.Provider = "microsoft.jet.oledb.4.0"
objc.ConnectionString = _
        "Data Source= " & Text1.Text & _
        "Extended Properties=Excel 8.0;"

objc.CursorLocation = adUseClient
objc.Open

objr.Open "select * from" & "[" & Combo1.Text & "$" & "]""", objc, adOpenDynamic, adLockOptimistic
'[sheet1$] ", objc, adOpenDynamic, adLockOptimistic"

Set MSHFlexGrid1.DataSource = objr

With MSHFlexGrid1
    For z = 0 To .Cols - 1
        .Row = 0
        .Col = z
        .CellFontBold = True
        .CellFontName = "Verdana"
        .CellAlignment = flexAlignCenterCenter
       
    Next
End With

MSHFlexGrid1.Rows - 1

Set objc = Nothing
Set objr = Nothing
Set XL = Nothing
Set WB = Nothing
Set SH = Nothing
End If
End Sub

declare the object like this.

Dim objc As New ADODB.Connection
Dim objr As New ADODB.Recordset

Dim XL As New Excel.Application
Dim SH As Excel.Worksheet
Dim WB As Excel.Workbook

declare the object like this.

Dim objc As New ADODB.Connection
Dim objr As New ADODB.Recordset

Dim XL As New Excel.Application
Dim SH As Excel.Worksheet
Dim WB As Excel.Workbook

Meenakshibansal:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?

Meenakshibansal, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer