Mengimpor Data Budget dari Excel ke Tabel di Access

Data budget disimpan di dalam tabel tblBudget. Tabel ini bisa diisikan melalui single form sebagai default view-nya. Mengenai pembuatan form ini, kita bisa menggunakan cara seperti telah dibahas pada posting yang berkaitan dengan cara membuat form, baik itu melalui wizard atau manual dengan menggunakan blank form. Setelah selesai dibuat, kita dapat menyimpan form budget dengan nama frmBudget seperti pada Gambar 1.

Gambar 1

Dalam prakteknya, memasukkan data budget ke dalam database adalah pekerjaan yang lumayan berat, karena data itu harus dimasukkan satu per satu untuk setiap kode rekening dalam satu bulan selama satu tahun. Di satu sisi, pengisian data budget ke dalam database biasanya selalu dikejar target, harus selesai dalam hitungan tak lebih dari satu minggu

Untuk memudahkan pengisian data budget, kita dapat membuat program untuk mengimpor data budget ke dalam database. Umumnya, data budget biasanya disusun dengan menggunakan aplikasi spreadsheet Excel, sehingga kita akan menggunakan file Excel untuk kemudian ditransfer ke Access. Berikut ini adalah langkah pembuatan form beserta kode VBA untuk mengimpor file Excel ke Access.

1. Buatlah form  dengan menggunakan Blank Form. Simpanlah form itu dengan nama frmImporBudget.
2. Pada bagian form detail, sisipkan text box untuk mengisikan lokasi folder beserta nama filenya. Properti yang perlu diubah untuk text box itu meliputi
   
   1. Name=nmFile
   2. Default Value==PreferensSistem("FolderPenyimpanan")
   3. Control Source dibiarkan kosong (unbound).
3. Sisipkan tombol perintah dengan properti
   
   1. Name=TampilkanData
   2. Caption= Tampilkan Data Impor.
4. Di sebelah kanan tombol perintah TampilkanData, sisipkan tombol perintah dengan properti
   
   1. Name=ImporData
   2. Caption=Impor Data Budget
   3. Enabled=No.
      Enabled akan sama dengan Yes bila eksekusi melalui tombol TampilkanData telah berjalan sempurna.
5. Sisipkan text box tanpa label di bagian kanan atas dari form detail dengan properti
   
   1. Name=ErrorMsg
   2. Control Source= ="Ada kesalahan impor data budget. Klik di sini untuk melihatnya"
   3. Visible=No
   4. Is Hyperlink=Yes
   5. Font Weight=Bold
   6. Fore Color= #ED1C24 (atau merah).
      
   Fungsi text box ini adalah untuk memberi dan menampilkan informasi pesan kesalahan pada saat data ditampilkan dan sebelum dilakukan transfer data ke tabel budget. Jadi bila ada kesalahan dalam proses impor data, pesan ini akan muncul dan memberitahu kesalahan macam apa yang terjadi yaitu dengan mengklik atau membuka link yang ada didalamnya. Untuk membuka link itu, aturlah properti event On Click= =PreviewUnRefresh("rptBudgetImpor Error",1), yaitu membuka report kesalahan macam apa yang terjadi selama proses impor data budget.
   
6. Di bagian paling bawah dari form detail, sisipkan subform dengan nama frmImporBudgetSubform dan Source Object kosong (unbound subform). Subform ini hanya akan muncul setelah tombol TampilkanData ditekan.

   

   Gambar 2

7. Tampilan design form dapat kita lihat pada Gambar 2. Untuk prosedur dan event procedure yang menyertai form frmImporBudget, kita dapat menggunakan kode VBA di bawah ini.
   
   view plainprint?

   1. Option Compare Database  
   2. Private Sub Form_Open(Cancel As Integer)  
   3.     Me.Caption = "Impor Data Budget " & Nz(IdPerusahaan("Nama"), "")  
   4.     If Not IsNull(Me.LoginPgn) Then Me.logout.Visible = True  
   5. End Sub  
   6. Private Sub ImporData_Click()  
   7.     Dim dbs As DAO.Database  
   8.     Dim qdf As DAO.QueryDef  
   9.     Dim fld As DAO.Field2  
   10.     Dim strSqla, strSqlb As String  
   11.     Dim n, i As Integer '1440 twips = 1 inch  
   12.     DoCmd.SetWarnings False  
   13.     Set dbs = CurrentDb()  
   14.     Set qdf = dbs.QueryDefs("qryBudgetImpor")  
   15.     n = qdf.Fields.Count  
   16.     i = 0  
   17.     strSqla = "INSERT INTO tblBudget (KodeRek, Deriv1, Deriv2, Tahun,Bulan, JumlahBudget) SELECT "  
   18.     For Each fld In qdf.Fields  
   19.       i = i + 1  
   20.       strSqla = strSqla & fld.Name & ", "  
   21.       If i = 3 Then Exit For  
   22.     Next fld  
   23.     i = 0  
   24.     For Each fld In qdf.Fields  
   25.       i = i + 1  
   26.       If i >= 4 Then  
   27.         strSqlb = strSqla & Val(Left(fld.Name, 4)) & " as Tahun, " & Val(Right(fld.Name, 2)) & " as Bulan, [" _  
   28.                 & fld.Name & "] from " & qdf.Name & ";"  
   29.         DoCmd.RunSQL strSqlb  
   30.       End If  
   31.     Next fld  
   32.     DoCmd.SetWarnings True  
   33. End Sub  
   34. Private Sub Proses_Click()  
   35.   Dim dbs As DAO.Database  
   36.   Dim qdf As DAO.QueryDef  
   37.   Dim fld As DAO.Field2  
   38.   Dim strSqla, strSqlb As String  
   39.   Dim n, i As Integer '1440 twips = 1 inch  
   40.   DoCmd.SetWarnings False  
   41.   Set dbs = CurrentDb()  
   42.   Set qdf = dbs.QueryDefs("qryBudgetImpor")  
   43.   n = qdf.Fields.Count  
   44.   i = 0  
   45.   DoCmd.SetWarnings False  
   46.   strSqla = "SELECT qryBudgetImpor.KodeRekUtama AS Kode, 'tidak ada dalam daftar rekening utama' AS Deskripsi " _  
   47.           & "INTO tblBudgetImporError FROM tblRekUtama RIGHT JOIN qryBudgetImpor " _  
   48.           & "ON tblRekUtama.KodeRek = qryBudgetImpor.KodeRekUtama WHERE (((tblRekUtama.KodeRek) Is Null));"  
   49.   DoCmd.RunSQL strSqla  
   50.   strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT qryBudgetImpor.Deriv1 AS Kode, " _  
   51.           & "'tidak ada dalam daftar rekening derivatif 1' AS Deskripsi FROM qryBudgetImpor " _  
   52.           & "LEFT JOIN tblRekDerivatif1 ON qryBudgetImpor.Deriv1 = tblRekDerivatif1.KodeDeriv1 " _  
   53.           & "WHERE (((tblRekDerivatif1.KodeDeriv1) Is Null));"  
   54.   DoCmd.RunSQL strSqla  
   55.   strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT qryBudgetImpor.Deriv2 AS Kode, " _  
   56.            & "'tidak ada dalam daftar rekening derivatif 2' AS Deskripsi FROM qryBudgetImpor " _  
   57.            & "LEFT JOIN tblRekDerivatif2 ON qryBudgetImpor.Deriv2 = tblRekDerivatif2.KodeDeriv2 " _  
   58.            & "WHERE (((tblRekDerivatif2.KodeDeriv2) Is Null));"  
   59.   DoCmd.RunSQL strSqla  
   60.   i = 0  
   61.   For Each fld In qdf.Fields  
   62.     i = i + 1  
   63.     If i < 4 Then  
   64.       If fld.Type <> 10 Then  
   65.         strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT '" & fld.Name & "' AS Kode, " _  
   66.                 & "'tipe data pada kolom " & fld.Name & " bukan text/alfabet/string' AS Deskripsi;"  
   67.         DoCmd.RunSQL strSqla  
   68.       End If  
   69.     Else  
   70.       If fld.Type < 2 Or fld.Type > 7 Then  
   71.         strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT '" & fld.Name & "' AS Kode, " _  
   72.                 & "'tipe data pada kolom " & fld.Name & " bukan angka' AS Deskripsi;"  
   73.         DoCmd.RunSQL strSqla  
   74.       End If  
   75.     End If  
   76.   Next fld  
   77.   DoCmd.SetWarnings True  
   78. End Sub  
   79. Private Sub TampilkanData_Click()  
   80.   If Not AdaFile(Me.nmFile) Then  
   81.     MsgBox "File atau folder tidak ada"  
   82.     Exit Sub  
   83.   End If  
   84.   Me.ErrorMsg.Visible = False  
   85.   Me.ImporData.Enabled = False  
   86.   Me.frmImporBudgetSubform.SourceObject = ""  
   87.   DoCmd.SetWarnings False  
   88.   Set dbsObject = Application.CurrentData  
   89.   For Each obj In dbsObject.AllTables  
   90.     If obj.Name = "tblBudgetImpor" Then  
   91.       DoCmd.DeleteObject acTable, obj.Name  
   92.     End If  
   93.   Next obj  
   94.   DoCmd.TransferSpreadsheet acImport, 9, "tblBudgetImpor", Me.nmFile, True  
   95.   DoCmd.SetWarnings True  
   96.   ImporBudgetDariExcel  
   97.   Me.frmImporBudgetSubform.SourceObject = "frmImporBudgetSubform"  
   98.   Me.frmImporBudgetSubform.Requery  
   99.   Proses_Click  
   100.   If DCount("*", "tblBudgetImporError") > 0 Then  
   101.     Me.ErrorMsg.Visible = True  
   102.     Me.ImporData.Enabled = False  
   103.   Else  
   104.     Me.ErrorMsg.Visible = False  
   105.     Me.ImporData.Enabled = True  
   106.   End If  
   107. End Sub  

   Option Compare Database
   Private Sub Form_Open(Cancel As Integer)
       Me.Caption = "Impor Data Budget " & Nz(IdPerusahaan("Nama"), "")
       If Not IsNull(Me.LoginPgn) Then Me.logout.Visible = True
   End Sub
   Private Sub ImporData_Click()
       Dim dbs As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim fld As DAO.Field2
       Dim strSqla, strSqlb As String
       Dim n, i As Integer '1440 twips = 1 inch
       DoCmd.SetWarnings False
       Set dbs = CurrentDb()
       Set qdf = dbs.QueryDefs("qryBudgetImpor")
       n = qdf.Fields.Count
       i = 0
       strSqla = "INSERT INTO tblBudget (KodeRek, Deriv1, Deriv2, Tahun,Bulan, JumlahBudget) SELECT "
       For Each fld In qdf.Fields
         i = i + 1
         strSqla = strSqla & fld.Name & ", "
         If i = 3 Then Exit For
       Next fld
       i = 0
       For Each fld In qdf.Fields
         i = i + 1
         If i >= 4 Then
           strSqlb = strSqla & Val(Left(fld.Name, 4)) & " as Tahun, " & Val(Right(fld.Name, 2)) & " as Bulan, [" _
                   & fld.Name & "] from " & qdf.Name & ";"
           DoCmd.RunSQL strSqlb
         End If
       Next fld
       DoCmd.SetWarnings True
   End Sub
   Private Sub Proses_Click()
     Dim dbs As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim fld As DAO.Field2
     Dim strSqla, strSqlb As String
     Dim n, i As Integer '1440 twips = 1 inch
     DoCmd.SetWarnings False
     Set dbs = CurrentDb()
     Set qdf = dbs.QueryDefs("qryBudgetImpor")
     n = qdf.Fields.Count
     i = 0
     DoCmd.SetWarnings False
     strSqla = "SELECT qryBudgetImpor.KodeRekUtama AS Kode, 'tidak ada dalam daftar rekening utama' AS Deskripsi " _
             & "INTO tblBudgetImporError FROM tblRekUtama RIGHT JOIN qryBudgetImpor " _
             & "ON tblRekUtama.KodeRek = qryBudgetImpor.KodeRekUtama WHERE (((tblRekUtama.KodeRek) Is Null));"
     DoCmd.RunSQL strSqla
     strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT qryBudgetImpor.Deriv1 AS Kode, " _
             & "'tidak ada dalam daftar rekening derivatif 1' AS Deskripsi FROM qryBudgetImpor " _
             & "LEFT JOIN tblRekDerivatif1 ON qryBudgetImpor.Deriv1 = tblRekDerivatif1.KodeDeriv1 " _
             & "WHERE (((tblRekDerivatif1.KodeDeriv1) Is Null));"
     DoCmd.RunSQL strSqla
     strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT qryBudgetImpor.Deriv2 AS Kode, " _
              & "'tidak ada dalam daftar rekening derivatif 2' AS Deskripsi FROM qryBudgetImpor " _
              & "LEFT JOIN tblRekDerivatif2 ON qryBudgetImpor.Deriv2 = tblRekDerivatif2.KodeDeriv2 " _
              & "WHERE (((tblRekDerivatif2.KodeDeriv2) Is Null));"
     DoCmd.RunSQL strSqla
     i = 0
     For Each fld In qdf.Fields
       i = i + 1
       If i < 4 Then
         If fld.Type <> 10 Then
           strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT '" & fld.Name & "' AS Kode, " _
                   & "'tipe data pada kolom " & fld.Name & " bukan text/alfabet/string' AS Deskripsi;"
           DoCmd.RunSQL strSqla
         End If
       Else
         If fld.Type < 2 Or fld.Type > 7 Then
           strSqla = "INSERT INTO tblBudgetImporError (Kode, Deskripsi) SELECT '" & fld.Name & "' AS Kode, " _
                   & "'tipe data pada kolom " & fld.Name & " bukan angka' AS Deskripsi;"
           DoCmd.RunSQL strSqla
         End If
       End If
     Next fld
     DoCmd.SetWarnings True
   End Sub
   Private Sub TampilkanData_Click()
     If Not AdaFile(Me.nmFile) Then
       MsgBox "File atau folder tidak ada"
       Exit Sub
     End If
     Me.ErrorMsg.Visible = False
     Me.ImporData.Enabled = False
     Me.frmImporBudgetSubform.SourceObject = ""
     DoCmd.SetWarnings False
     Set dbsObject = Application.CurrentData
     For Each obj In dbsObject.AllTables
       If obj.Name = "tblBudgetImpor" Then
         DoCmd.DeleteObject acTable, obj.Name
       End If
     Next obj
     DoCmd.TransferSpreadsheet acImport, 9, "tblBudgetImpor", Me.nmFile, True
     DoCmd.SetWarnings True
     ImporBudgetDariExcel
     Me.frmImporBudgetSubform.SourceObject = "frmImporBudgetSubform"
     Me.frmImporBudgetSubform.Requery
     Proses_Click
     If DCount("*", "tblBudgetImporError") > 0 Then
       Me.ErrorMsg.Visible = True
       Me.ImporData.Enabled = False
     Else
       Me.ErrorMsg.Visible = False
       Me.ImporData.Enabled = True
     End If
   End Sub

Sumber data budget yang akan diimpor disimpan dalam format Excel 1997-2000. Jadi ekstensinya menggunakan .xls, misalnya importbudget.xls, impor.xls, dan sebagainya. Sel awal pada Excel berada di A1 yang merupakan sel untuk judul kolom Kode Rekening Utama, kemudian diikuti Deriv1, Deriv2, yyyym1, yyyym2, dan seterusnya, di mana yyyy adalah nama tahun, m1 adalah bulan pertama, m2 adalah bulan kedua, sampai dengan bulan ke-12. Format selengkapnya tampak seperti Gambar 3

Gambar 3

Di Gambar 3, tiga kolom utama bisa diberi judul dengan nama apapun, yang penting unik. Sedangkan kolom ke-4, ke-5, dan seterusnya harus mengikuti pola yyyymm di mana yyyy adalah tahun dan mm adalah bulan. Pada gambar itu, tahun yang digunakan adalah 2014, sedangkan bulan dimulai dari bulan 01 dan diakhiri bulan 12. Jadi kolom itu diberi judul 201401, 201402, ..., 201412.

Untuk kolom dengan tahun yang berbeda, pola yang digunakan tetap sama, misalnya 201304, 201305,...,201403, yang berarti kolom itu dimulai bulan April 2013 dan berakhir bulan Maret 2014. Sebelum dipindahkan ke tabel tblBudget, data impor itu ditampung dahulu di sebuah tabel temporer yang kemudian ditampilkan di form frmImporBudget untuk proses pengecekan manual, seperti tampak pada Gambar 4. Bila tidak ada kesalahan, maka data impor dapat disimpan di tabel tblBudget dengan menekan tombol Impor Data Budget.

Gambar 4

Data yang telah tersimpan di tblBudget hanya bisa diperbaiki melalui form frnImporBudget secara satu per satu. Jadi, agar tidak membuang waktu percuma, kita harus memastikan bahwa tabel budget yang diimpor dari Excel telah sesuai dengan apa yang diinginkan.

Gambar 5

Laporan Kesalahan Impor Data Budget

Untuk menampilkan kesalahan selama proses impor data budget, kita harus membuat report kesalahan impor budget. Bentuk report kesalahan itu sederhana, seperti tampak pada Gambar 5. Record source report ini diambil dari tblBudgetImpor Error yang dihasilkan saat kita menekan tombol impor Tampilkan Data Impor (TampilkanData).