• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This code can be used to read an MS-EXCEL file. U need to select the Excel File and the sheet name,

This code can be used to read an MS-EXCEL file. U need to select the Excel File and the sheet name,

This code can be used to read an MS-EXCEL file. U need to select the Excel File and the sheet name, and u can manupulate it as if it is a

API Declarations


'\ Put Three Command Button "cmdClose" and "CmdReadData" and "cmdBrow"
Commonduialog Box named "cdlgBrow"
Tow Text Box named "txtFileName" and "TxtSheetName"

Public gSConn As New ADODB.Connection '\ Source Connection
Public sDBName As String, sUID As String, sPWD As String
Dim rs As New ADODB.Recordset




Rate This code can be used to read an MS-EXCEL file. U need to select the Excel File and the sheet name,

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    cdlgBrow.InitDir = App.Path
    cdlgBrow.ShowOpen
    If cdlgBrow.FileName <> "" Then
        txtFileName = Trim(cdlgBrow.FileName)
    End If
End Sub

Private Sub CmdReadData_Click()
Me.MousePointer = vbHourglass
On Error GoTo ErrHand

Dim ctr As Double
Dim strIns As String, sConnStr As String
Dim dblTotRow As Integer

'\\ Connecting to Source
    sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim(txtFileName) & ";Extended Properties=Excel 8.0"
    gSConn.Open sConnStr

'\\ Opening Recordset
strsql = "SELECT * FROM [" & txtSheetname & "$]"
rs.Open strsql, gSConn, adOpenStatic


dblTotRow = rs.RecordCount

For i = 1 To dblTotRow
    ' this point onwards    u can proceed like a normal recordset manupulation
    For j = 0 To rs.Field.Count
        Debug.Print rs.Fields(j).Value
    Next j

    Debug.Print vbCrLf

    rs.MoveNext
Next

Me.MousePointer = vbDefault
Exit Sub
ErrHand:
MsgBox Err.Description, vbInformation, "upload Error"
Me.MousePointer = vbDefault
Err.Clear
End Sub