Cara Transfer Data Tabel Access ke Sheet Excel Menggunakan Macro VBA

Kami sebelumnya telah membahas Tips Mentransfer Data Dari Sel Excel Ke Tabel MS Access, untuk kali ini kami akan berbagi cara mentransfer sebuah tabel dari database Access ke dalam worksheet Excel, dimana Excel memiliki kemampuan dan keistimewaan tersendiri dalam mengolah data.

Menyajikan sebuah table database Access ke dalam worksheet Excel adalah suatu hal yang sangat mudah dan cepat untuk dilakukan secara manual, namun untuk membuatnya agar lebih cepat dan efisien lagi maka diperlukan sebuah kode macro.

Kami ingatkan Anda kembali tentang cara mengimpor data access ke dalam Excel secara manual sebagai berikut:

 

Cara Import Data Tabel Access ke Sheet Excel Manual 

1. Buka menu ribbon Data > Get External Data > From Access.
2. Pada jendela Select Data Source pilih file-nya lalu klik Open.
3. Akan muncul kotak dialog Import Data, pastikan tombol opsi Table dan Existing worksheet: dipilih lalu klik OK.
4. Data yang dihasilkan berupa sebuah tabel Excel, namun jika Anda ingin mengubahnya menjadi barisan sel biasa di worksheet maka pada ribbon tab Table Tools - Design di grup tab Tools klik Convert to Range atau bisa juga dengan cara klik kanan tabel kemudian pilih Table > Convert to Range.
5. Akan ada konfirmasi 'This will permanently remove the query definition from the sheet and convert the table to a normal range. Continue?' klik OK.
6. Untuk menghilangkan garis tabel dan warnanya Anda bisa mengubah style table Excel menjadi normal dengan cara sorot seluruh tabel (bisa dengan cara menekan tombol CTRL+A keyboard lalu pada menu ribbon Home di grup Styles klik Cell Styles lalu pilih Normal.

Cara Import Data Tabel Access ke Sheet Excel dengan Macro VBA

Contoh di bawah ini bertujuan untuk menyalin keseluruhan data yang tercatat pada Table1 Access ke Sheet1 di Excel. 

Untuk dapat mengekspor data pada Table1 database Access, maka Anda harus menetapkan recordset dengan kode SQL pada sambungan.

Berikut ini langkah-langkah yang harus Anda ikuti:

1. Seperti yang sudah dijelaskan sebelumnya, adapun langkah pertama yang wajib Anda lakukan adalah membuka VBE di Excel kemudian mengaktifkan Microsoft ActiveX Data Objects 2.8 Library pada menu Tools > References... kemudian tandai centang lalu klik OK. Buka menu Insert > Module.

2. Ketik dan tekan enter kode berikut pada Module:

Sub TrfAccessKeExcel()

3. Tepat di bawah kode di atas Anda tetapkan contoh variabel berikut:

Dim A As ADODB.Connection
Dim B As ADODB.Recordset
Dim C As String
Dim D As String
Dim E As Worksheet

4. Berikan tugas untuk masing-masing variabel:

Set A = New ADODB.Connection
Set B = New ADODB.Recordset
Set E = Worksheets("Sheet1")

5. Silakan ubah alamat file database access yang sesuai di dalam komputer Anda:

C = "C:\Alamat\File\Anda\Database1.accdb"

6. Tulis kode ini untuk menetapkan penyedia sambungan database.

A.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" _
& C & ";Persist Security Info=False;"

7. Kode ini adalah statement pemrograman SQL yaitu SELECT dan FROM untuk mengimpor data di Table1:

D = "SELECT Table1.* FROM Table1;"

8. Tuliskan perintah Clear pada worksheet yang dituju.

E.Cells.Clear
With A

9. Anda mulai membuka sambungan dengan perintah:

.Open

10. Kode ini untuk memutus recordset:

.CursorLocation = adUseClient
End With
With B

11. Membuat recordset dengan kode berikut: 

.Open D, A

12. Memutus recordset:

Set .ActiveConnection = Nothing
End With

13. Kode berikut untuk menyalin data table1 ke Sheet1 dimulai dari sel A2, dimana Baris 1 berisi judul:

E.Range("A2").CopyFromRecordset B

14. Dudukkan kembali judul kolom isian seperti pada contoh di postingan sebelumnya yang mana berjumlah 6 buah kolom. Kolom ID ikut ditransfer juga pada kolom A di Excel, jadi Anda bisa saja menghapus kolom A tersebut jika tidak diperlukan. Silakan tulis kode:

E.Range("A1:G1").Value = _
Array("ID", "NamaDepan", "NamaBelakang", "JenisKelamin", "Jabatan", "Alamat", "Tahun")

15. Hentikan recordset:

B.Close

16. Hentikan sambungan:

A.Close

17. Lepaskan memori variabel objek:

Set B = Nothing
Set A = Nothing
Set E = Nothing

Kode lengkapnya sebagai berikut:

Sub TrfAccessKeExcel()
Dim A As ADODB.Connection
Dim B As ADODB.Recordset
Dim C As String
Dim D As String
Dim E As Worksheet
Set A = New ADODB.Connection
Set B = New ADODB.Recordset
Set E = Worksheets("Sheet1")
C = "C:\Alamat\File\Anda\Database1.accdb"
A.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" _
& C & ";Persist Security Info=False;"
D = "SELECT Table1.* FROM Table1;"
E.Cells.Clear
With A
.Open
.CursorLocation = adUseClient
End With
With B
.Open D, A
Set .ActiveConnection = Nothing
End With
E.Range("A2").CopyFromRecordset B
E.Range("A1:G1").Value = _
Array("ID", "NamaDepan", "NamaBelakang", "JenisKelamin", "Jabatan", "Alamat", "Tahun")
B.Close
A.Close
Set B = Nothing
Set A = Nothing
Set E = Nothing
End Sub

Silakan Anda coba kode macro yang telah Anda susun. 

Selamat mencoba dan semoga bermanfaat.

Share :