VB6: Menampilkan Data dari Excel ke Datagrid

Back to VB6 dulu hehe...

Lagi pengen bahas gimana cara nya mengambil data dari excel kemudian memuatnya dalam ADODB recorset dan menampilkan pada datagrid.

Sebagai contoh kita punya file excel bernama sample.xls (inget .xls yah bukan .xlsx karena tahun 1998 excel 2007 belom lahir heuheu)

Buat sebuah project visual basic 6 standard, kemudian tambahkan common dialog dan datagrid control pada toolbox.

Tambahkan juga reference Microsoft ActiveX Data Object.

Design UI:

!!! UPDATE
Mohon maaf klo kurang jelas, lstTable itu control ListBox yang rani kasih nama lstTable

Code:
Option Explicit
Dim xConn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim rsExcel As ADODB.Recordset
Dim strExcel As String

Private Sub Open_Excel(FilePath As String)
On Error GoTo err_Handler

    Set xConn = New ADODB.Connection
    With xConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & FilePath & _
                            ";Extended Properties=Excel 8.0;"
        .Open
    End With

Exit Sub
err_Handler:
    MsgBox "Error on Open_Excel :" & Err.Number & " " & Err.Description
End Sub

Private Sub List_Table()
On Error GoTo err_Handler

    lstTable.Clear
    Open_Excel Me.txtFile.Text
    Set rsTable = xConn.OpenSchema(adSchemaTables)
    Do While Not rsTable.EOF
    lstTable.AddItem rsTable.Fields("TABLE_NAME").Value
    rsTable.MoveNext
    Loop
    Set rsTable = Nothing
    xConn.Close
    If Not Me.lstTable.ListCount = 0 Then
       Me.lstTable.ListIndex = 0
       Data_Excel Me.lstTable.Text
    End If

Exit Sub
err_Handler:
    MsgBox "Error on Open_Excel :" & Err.Number & " " & Err.Description
End Sub

Private Sub Data_Excel(Sheet As String)
Open_Excel Me.txtFile.Text

Set rsExcel = New ADODB.Recordset
strExcel = "SELECT * FROM [" & Sheet & "]"

With rsExcel
    .CursorLocation = adUseClient
    .Open strExcel, xConn, adOpenKeyset, adLockReadOnly
    .ActiveConnection = Nothing
End With
Set Me.grdExcel.DataSource = rsExcel
xConn.Close
End Sub

Private Sub cmdBrowse_Click()
dlgOpen.Filter = "Excel Files (*.xls)|*.xls"
dlgOpen.ShowOpen
Me.txtFile.Text = dlgOpen.FileName
End Sub
Untuk load data excel, me list table/sheet, dan menampilkan di datagrid.
Private Sub cmdLoad_Click()
If txtFile.Text = "" Then _
   MsgBox "Pilih file excel untuk proses import": Exit Sub
If LCase(Right$(txtFile.Text, 4)) <> ".xls" Then _
   MsgBox "File harus dalam format Excel(.xls)": Exit Sub
List_Table
End Sub

Private Sub lstTable_Click()
If Not Me.lstTable.ListCount = 0 Then
   Data_Excel Me.lstTable.Text
End If
End Sub

Private Sub txtFile_Change()
  lstTable.Clear
  Set grdExcel.DataSource = Nothing
End Sub

Private Sub cmdExit_Click()
Unload Me
End Sub

Runtime:

Click here if you like this article.


Baca Juga:
VB6: Export to Excel