Home > Visual Basic > Membaca file Excel dan Menampilkan datanya

Membaca file Excel dan Menampilkan datanya

February 13, 2011

Ceritanya kali ini kita akan membuka file excel dan menampilkan datanya, dimana pengembangan artikel ini bisa digunakan untuk membuat program impor atau konversi data sederhana, yang mana hal ini sering kita lakukan pada saat implementasi sistem.

Karena biasanya permintaan dari klien data-data manual mereka yang biasa menggunakan format excel dikonversi ke sistem baru yang kita buat.

Nah untuk memudahkan pembacaan file excel, biasanya formatnya kita buat sesederhana mungkin. Contoh :

Atau klo mau dikasir warna dan border juga boleh😀

Nah berhubung postingan kali ini di “TAG” visual basic 6, otomatis sample programnya juga menggunakan bahasa ini.

Pertama kita buat dulu fungsi untuk membuka file ini (excel-red) dan jangan lupa tambahkan referensi Microsoft ActiveX Data Objects 2.xx library

Dim conXls As ADODB.Connection

Private Function openExcelFile(ByVal excelFile As String) As Boolean
    On Error GoTo errHandle

    Set conXls = New ADODB.Connection
    conXls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Replace(excelFile, Chr$(0), "") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    conXls.Open

    openExcelFile = True

    Exit Function
errHandle:
    openExcelFile = False
End Function

Fungsi diatas adalah fungsi standar yang biasa kita gunakan untuk melakukan koneksi ke database seperti Ms Access, SQL Server, MySQL, Firebird de el el, hanya string koneksinya aja yang berbeda.

Berikut contoh penggunaan fungsi diatas.

Private Sub cmdTest_Click()
    Dim rsExcel     As ADODB.Recordset
    Dim strSql      As String

    List1.Clear

    ' misal kita membuka file data_siswa.xls yg ada di drive c
    If openExcelFile("c:\data_siswa.xls") Then

        ' selanjutnya data yg diambil ada di sheet1
        ' sheet disini sama seperti tabel yang ada di database
        strSql = "SELECT * FROM [Sheet1$]" ' penting !!!, jangan lupa menambahkan karakter $
        Set rsExcel = New ADODB.Recordset
        rsExcel.Open strSql, conXls, adOpenForwardOnly, adLockReadOnly
        If Not rsExcel.EOF Then
            Do While Not rsExcel.EOF

                ' tampilkan data yg ada sheet1 ke ListBox
                List1.AddItem rsExcel(1).Value & ", " & rsExcel(2).Value

                rsExcel.MoveNext
            Loop
        End If
        rsExcel.Close
        Set rsExcel = Nothing
    End If
End Sub

dan hasilnya

Selamat mencoba🙂

  1. July 17, 2011 at 4:09 am

    numpang Tax, kemarin saya membuat program dgn database di SQL tetapi, pas mau di modiv programx kok ngak mau simpan datax gy ya…. udh saya ganti ke Ms Acsess jg sm aja… ngak ada eror pada kodingx tp data yang saya inputkan ngak masuk…kira”masalahx dimana ya? mohon bantuanX…..

    • July 17, 2011 at 5:15 am

      pake error handle enggak? klo iya coba dinonaktifkan dulu setelah itu baru coba nyimpan lagi

  2. kvee
    October 28, 2011 at 4:34 am

    pak saya mau tanya saya masih belajar dalam vb6 saya coba membuat file setup pada windows xp sp3, saya sudah masukkan file2 .ocx/.dll saat membuat file setup dan ketika saya instal pada komputer lain misal.xp sp2. selalu ada kata2 inti nya file lebih baru dari yg ada,,restart untuk update, tp begitu saya restart dan instal lg eror lg kaya tadi..itu knp y pak??

    • October 29, 2011 at 2:04 am

      Buat packagenya pake om ? Klo pake Package & Deployment Wizard memang sering muncul kasus seperti itu mendingan pake Inno Setup atau NSIS aja.

  3. Kholil
    January 7, 2012 at 4:00 pm

    Terima kasih infonya,Tapi setelah saya praktekkan ke format xxxx.xlsx (excel 2007),kok tidak bisa membaca pak?yang harus dirubah bhs yang mana Pak?Terima atas jawabannya.

    email saya : sm2untung@yahoo.com

    • January 9, 2012 at 8:11 am

      Pada connection string, coba ganti nilai

      Provider=Microsoft.Jet.OLEDB.4.0;
      

      Menjadi

      Provider=Microsoft.ACE.OLEDB.12.0;
      
      • Kholil
        January 30, 2012 at 12:48 am

        Terima kasih, sudah bisa pak.Mau tanya lagi ni,Jika di excel ,contennya dihapus dengan cara klik kanan “Clear contents”,knp kalau menggunakan perintah INSERT kok cell yang dihapus tadi diloncati,dianggap ada contennya,? Terimakasih jawabannya.

        > sm2untung@yahoo.com

        • January 30, 2012 at 4:37 pm

          Maksudnya gimana ya om? Perintah INSERTnya dijalankan dari VB gitu ?

          • Kholil
            January 30, 2012 at 5:22 pm

            Maksudnya gini pak, data di Excel di Hapus langsung ke excel nya bukan lewat vb , dengan cara klik kanan “Clear contents” , selanjutnya di INSERT lewat VB,

            Cell yang dihapus dengan cara clear contents (bukan delete),kok diloncati setelah pakai Perintah INSERT lewat VB.,

            Maaf jika bahasanya mbulet.

            • January 31, 2012 at 9:10 am

              Kok enggak pake property Cell aja om untuk mengisi data diexcelnya

            • dudu dudidam
              January 8, 2013 at 8:01 am

              excel selalu gitu dia nyimpen history atau bahasa kerenya cache, dibershkan aja cachenya

  4. January 9, 2012 at 9:49 am

    Mas admin ada contohnya gak sourcode convert database mysql ke excel …misalkan kita ingin convert tb_xxx dari mysql ke excel, terimaksih mas admin sebelumnya …

    • January 9, 2012 at 4:28 pm

      Berarti setiap tabel menjadi sheet ?

      • Abdillah
        January 11, 2012 at 9:26 am

        contoh sederhana aja mas, 1 tabel yang ada di datagrid/listview di convert ke excel …

  5. Tholomew
    March 29, 2012 at 9:47 pm

    Pak maaf nih pakk bnr bnr newbie nihh
    kalau kode yang kotak pertama itu di masukinnya kmn??
    Terima Kasih

    • March 30, 2012 at 9:39 am

      Pertanyaannya belum jelas om

  6. aryan
    May 18, 2012 at 12:09 am

    saya ingin membuka sebuah program, tapi harus mengkopi ARPopUpMenu.ocx, cNewMenu6.dll, dan ssubtmr6.dll di system32 dan selalu gagal/eror kira2 solusinya bagaimana ya mas? dan saya msh belum mengerti fungsi dari ke3 komponen trsebut apa. mohon pencerahannya🙂

    • May 19, 2012 at 9:21 am

      Maksudnya gagal gimana om ? Kan langkah-langkahnya ada 2:
      1. Mengcopy file dll/ocx ke windows\system32
      2. Melakukan registrasi file dll/ocx menggunakan perinta regsvr32 nama file dll/ocx

  7. Hendra
    July 28, 2012 at 8:18 am

    Om.. mo tanya, kalau untuk menampilkannya di List View bisa ga ya…? thanks.

    • July 28, 2012 at 9:04 am

      Pertama setting dulu listviewnya di event Form_Load

      Private Sub Form_Load()
          Dim x As Long
      
          With ListView1
              .View = lvwReport
              .GridLines = True
              .ColumnHeaders.Add , , "No.", 500
              .ColumnHeaders.Add , , "NIS", 1500
              .ColumnHeaders.Add , , "Nama", 4700
          End With
      End Sub
      

      Kemudian revisi event cmdTest_Click :

      Private Sub cmdTest_Click()
          Dim rsExcel     As ADODB.Recordset
          Dim strSql      As String
          Dim noUrut      As Long
          
          ListView1.ListItems.Clear
      
          ' misal kita membuka file data_siswa.xls yg ada di drive c
          If openExcelFile("c:\data_siswa.xls") Then
      
              ' selanjutnya data yg diambil ada di sheet1
              ' sheet disini sama seperti tabel yang ada di database
              strSql = "SELECT * FROM [Sheet1$]" ' penting !!!, jangan lupa menambahkan karakter $
              Set rsExcel = New ADODB.Recordset
              rsExcel.Open strSql, conXls, adOpenForwardOnly, adLockReadOnly
              If Not rsExcel.EOF Then
                  noUrut = 1
                  Do While Not rsExcel.EOF
      
                      ' tampilkan data yg ada sheet1 ke ListView
                      With ListView1
                          .ListItems.Add , , noUrut
                          .ListItems(noUrut).SubItems(1) = rsExcel(1).Value
                          .ListItems(noUrut).SubItems(2) = rsExcel(2).Value
                      End With
                      
                      noUrut = noUrut + 1
                      rsExcel.MoveNext
                  Loop
              End If
              rsExcel.Close
              Set rsExcel = Nothing
          End If
      End Sub
      

      Belum sy coba mudah2xan bisa😀

  8. Hendra
    August 1, 2012 at 2:54 pm

    Oke..makasih banyak, sangat membantu sekali..

    • August 4, 2012 at 10:19 am

      Sama2x om

  9. September 7, 2012 at 7:57 am

    assalamualaikum, salam kenal.
    mas, kalau untuk meng-input dan menghapus data bisa gak mas??? excellnya dijadikan database untuk menggantikan ms.acces. gimana caranya ya mas???

    terimakasih sebelumnya dan mohon bantuannya,

  10. September 16, 2012 at 10:25 pm

    Terima kasih bgt ya mas admin…manfaat bgt nih untuk client yg minta datanya diambil dr exel…makasih bgt…manfaat bgt nih…

  11. ARDANA
    September 28, 2012 at 10:19 pm

    Manteb gan……….
    klo di VB.NET codingnya gimana ya……?
    Maaf aq dah nyari2 tp ga ktemu…….. pokoknya dah Mumeeeee.com deh…..
    atas bantuan dan pencerahannya, Hatur nuhun……

  12. Hery
    November 26, 2012 at 2:35 pm

    Mantaaap

  13. March 5, 2013 at 2:04 pm

    om, kalau mau import dari excel ke mysql, codingnya gimana om, saya dapet coding kaya gini
    http://www.facebook.com/groups/programmervbindonesia/10151475631884271/
    keluar error kaya gini

    mohon pencerahnya, makasi

    • March 16, 2013 at 4:30 pm

      Sory om sy enggak punya akun FB😳

  14. danz
    June 20, 2013 at 2:36 pm

    mas klo nampilin field nya juga gmana, itu kan yg nampil hnya Isi dr Nis, sedangkan NIS, Nama ga tampil cara nampilin nya bagaimana ya mas.. terima kasih asslamulaikum

    • June 24, 2013 at 1:44 pm

      Tinggal direvisi sedikit kode pada saat membuat file excelnya.

      conXls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Replace(excelFile, Chr$(0), "") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
      

      Kode di atas untuk nilai properties HDRnya kan diset Yes, tinggal diganti menjadi No

  15. ifan daru
    September 6, 2013 at 1:58 pm

    mas kalo mau konversi file .txt ke .txt tapi dengan susunan baris berbeda gimana ya mas…contoh txt1 isi “1234567890” nah saya mau ubah ke txt juga tapi isinya menjadi “6789012345”…hehehhe maaf mas masih baru belajar…makasih banyak sebelumnya….

    • September 6, 2013 at 3:17 pm

      Untuk merubah posisinya om bisa menggunakan fungsi string left dan right

      Dim angka1  As String
      Dim angka2  As String
      
      angka1 = "1234567890"
      angka2 = Right(angka1, 5) & Left(angka1, 5)
      
      Debug.Print angka2
      

      kemudian untuk menulis ke file text ada banyak cara, untuk contohnya om bisa lihat disini.

  16. December 20, 2013 at 10:56 am

    Mas saya mau tanya, kalau coding buat impor data excel ke mysql di vb6 , codingnya bagaimana, komponen nya ada common dialognya ?

    • December 23, 2013 at 1:43 pm

      1. Kode untuk penggunaan, komponen CommonDialog itu sangat sederhanya sekali, contoh :

      Private Sub Command1_Click()
          With CommonDialog1
              .ShowOpen
              
              MsgBox "Nama file yg dipilih : " & .FileName
          End With
      End Sub
      

      2. Untuk membaca file excel dari VB 6, sebenarnya hanya berbeda di string koneksinya saja, om bisa coba seperti berikut :

      Private Sub Command1_Click()
          Dim connXLS     As ADODB.Connection
          Dim namaFile    As String
          Dim sql         As String
          
          namaFile = "c:\nama_file_excel.xl"
          
          Set connXLS = New ADODB.Connection
          connXLS.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Replace(namaFile, Chr$(0), "") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
          connXLS.Open
          
          strSql = "SELECT * FROM [NAMA_SHEET$]"
          ' TODO :
          ' 1. Jalankan perintah SELECT
          ' 2. Lakukan perulangan
          ' 3. Insert ke tabel mysql
      End Sub
      
  17. Ruky
    February 22, 2014 at 10:19 am

    mas koding tersebut kan hanya untuk menampilkan data dari Excel ke VB. 6, pertanyaannya apakah data dari excel tersebut sudah tersimpan dalam databasenya…? kalau tidak bagaimana caranya agar data dari excel tersebut bisa tersimpan dalam database acces. istilah gampangnya fasilitas impor dari data excel ke database acces 2003 menggunakan VB. 6?

    • March 4, 2014 at 9:55 am

      Ya perintah :

      ' tampilkan data yg ada sheet1 ke ListBox
      List1.AddItem rsExcel(1).Value & ", " & rsExcel(2).Value
      

      tinggal om ganti dengan perintah SQL “insert into …”

      • Ruky
        March 6, 2014 at 12:06 pm

        lengkapnya gimana mas…,

        • March 8, 2014 at 9:25 am

          Om sendiri klo mau nyimpan kedatabase perintahnya gimana ?

  1. No trackbacks yet.
Comments are closed.
%d bloggers like this: