Friday, March 2, 2018

Eksport & Import di Excel dengan bantuan ADO, VBA dan SQL (Bag. 2)


Import dari Access ke Excel dengan VBA, ADO dan SQL (Late Binding)

Untuk bahasan sekilas mengenai ADO silahkan buka disini.

Database yang akan kita gunakan adalah Database Mahasiswa yang mana didalamnya ada Tabel Mahasiswa. Silahkan download file database dan Excel nya disini dan download ke dalam folder yang sama.

Didalam Tabel Mahasiswa tersebut terdapat beberapa field dengan tipe dan format seperti berikut :

Tabel Mahasiswa

Sedangkan untuk file Excel, ada dua Sheet. Sheet pertama bernama Input yang akan digunakan untuk Eksport dari Excel ke Access. Kolom-kolom yang ada di Sheet ini sama persis dengan yang ada pada Tabel Mahasiswa. Begitu juga formatnya.


Sheet berikutnya adalah Sheet Laporan yang akan digunakan sebagai tempat untuk Import data dari Access. Cara pertama adalah menggunakan metode Late Binding untuk meng import Tabel Mahasiswa. Ikuti langkah-langkah berikut :

1.       Buka file Excel Mahasiswa dan buka VBE (Alt + F11)
2.       Insert module baru
3.       Buat Prosedur baru. Di contoh dibawah prosedurnya saya beri nama ImportTabel
4.       Tuliskan skrip seperti dibawah :


Penjelasan Skrip :


Karena metode yang kita gunakan adalah Late Binding, artinya kita tidak mengatur referensi ADO Library melalui menu Tools à Refference melainkan menuliskan langsung dalam skrip VBA.

1.       Variabel conn akan kita gunakan sebagai ADO Connection
2.       Variabel rs akan kita gunakan sebagai ADO Recordset
3.       Variabel connstr akan kita gunakan sebagai Connection String
4.       Variabel dbpath adalah lokasi tempat dimana file Access atau Database berada
5.       Sedangkan sqlstr adalah variabel yang akan kita gunakan menuliskan SQL Query
6.       Karena variabel conn dideklarasikan sebagai Obyek maka berikutnya kita wajib menggunakan statetemen Set untuk menciptakan Obyek ADODB Connection nya.
7.       Hal yang sama untuk variabel rs. Karena sudah di deklarasikan sebagai Obyek maka kita wajib menciptakan Obyek ADODB recordset melalui statement Set dan CreateObject

Skrip berikutnya adalah :



8.       Isi variabel dbpath dengan mengambil path dimana file Excel Mahasiswa.xlsm berada (Thisworkbook.path) dan menggabungkan string nya dengan string nama Databasenya yaitu Mahasiswa.accdb. Jangan lupa tanda “\” sebelum Mahasiswa.
9.       Isi variabel connstr adalah Connection String untuk Database MS Access 2013 ditambah dengan Data Source nya yang diambil dari isi variabel dbpath . Tipe Access Database 2007 keatas dapat menggunakan Connection String seperi diatas. Untuk menentukan jenis Connection String yang tepat untuk masing-masing Database dapat dilihat di www.connectionstring.com
10.       Karena kita akan mengambil isi dari semua Field dalam tabel Mahasiswa maka variabel sqlstr kita isi dengan query SQL SELECT * From Mahasiswa
11.       Setelah definisi path Database, Connection String dan SQL Query nya beres kita coba buka koneksi nya. Syntax nya : connection.open connection string nya.
12.       Setelah koneksi sudah terbuka, kita coba buka Recordset nya. Baris ini membuka Recordset menggunakan query dalam variabel sqlstr menggunakan Active Connection dalam variabel conn

Sampai dengan tahap ini coba untuk gunakan tombol F8 untuk proses Debug satu-satu. Apabila penulisan skrip tidak ada yang salah maka tidak akan muncul Error.
Skrip berikutnya adalah :


13.   Baris ini berfungsi untuk menghapus apabila sudah ada data hasil proses sebelumnya.
14. Baris berikutnya langsung mencetak semua Recordset pada Sheet Laporan mulai dari Range A2 menggunakan metode CopyFromRecordset
15.   Seperti yang sudah dibahas sebelumnya, Recordset yang sudah dibuka sebelumnya wajib untuk ditutup kembali
16.   Sama seperti diatas, Connection yang sudah dibuka sebelumnya wajib untuk ditutup kembali
17.    Baris ini dan 18 berfungsi untuk membersihkan isi variabel rs dan conn dari Memory.

Untuk sementara ini dulu. Semoga bermanfaat.

No comments:

Post a Comment