Belum lama ini, saya seorang rekan meminta bantuan dibuatkan sebuah program kecil, yaitu progam untuk membuat cetakan invoice dari file excel. Awalnya saya ingin membuatkan program kecil yang paten menggunakan Visual Basic dan MySQL sebagai database. Tetapi karena keterbatasan waktu saya memutuskan untuk memakai VBA, agar embedded langsung dengan file excel. Tiba - tiba punya ide untuk membuat program dari Visual Basic tetapi menggunakan file Excel ( .xls atau .xlsx ) sebagai database. Setelah browsing kesana kemari akhirnya menemukan caranya, berikut dasar - dasar pemrograman Visual Basic dengan Microsoft Excel.
Pertama yaitu membuat Connection String, yaitu menggunakan provider Jet OLEDB 4.0, pada Visual Basic - References ,saya menggunakan Microsoft ActiveX Data Object 2.6, berikut syntax connection string.
Set Conn = New ADODB.Connection
With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path &; _
"\file.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""
.CursorLocation = adUseClient
.Open
End With
Option HDR = Yes, artinya pada kolom pertama pada worsheet yang dipilih akan menjadi nama field / header.Kemudian Option IMEX=1 yaitu menerima data dari kolom excel dengan berbagai tipe data. Perlu diingat bahwa column di Excel berbeda dengan field pada table di file database dimana 1 field hanya mempunyai 1 tipe data, sedangkan di file excel bisa banyak tipe.
Kemudian untuk penggunakan Query / SQL standart, hanya perlu diingat pengganti table pada Excel adalah worksheet, contoh syntax penggunaan SQL ada di bawah ini :
strquery = "SELECT * FROM [Sheet1$B1:B1124] WHERE COMPONENTS LIKE '%PANEL%'"
oRS.Open strquery, oConn, adOpenDynamic, adLockReadOnly
If Not oRS.BOF Then
Do While Not oRS.EOF
If Trim(oRS.Fields(0).Value) <> "" Then
List2.AddItem vbTab & oRS.Fields(0).Value
End If
oRS.MoveNext
Loop
End If
oRS.Close
Pada syntax SQL di atas, table digantikan dengan range baris data yaitu dari baris B1 s/d B1124, kemudian field COMPONENTS adalah salah satu kolom pada file Excel yang akan dicari datanya. Jika anda mendapatkan Error "
No value given for one or more required parameters", maka kemungkinan adalah kolom / field yang anda cari setelah syntax WHERE, itu tidak termasuk dalam baris data setelah syntax FROM, jadi disini anda perlu lebih teliti.
Kemudian untuk membuat query dengan banyak nilai kosong / null, bisa menggunakan sintak seperti dibawah ini:
sSQL = " SELECT [CODE], " _
" IIF([COMPONENTS] is null, ' ', [COMPONENTS]) AS [COMPONENTS], " _
" FROM [Sheet1$A1:L1124] " _
" WHERE CODE = '" & Code_Components & "'"
oRs.Open sSQL, oConn, adOpenStatic
If Not oRs.BOF Then
'statement
End If
oRs.close
Awalnya saya ingin membuat file Excel tersebut untuk menyimpan data Invoice, tapi hal itu membuat file Excel menjadi bengkak ukuran-nya. Akhirnya saya memutuskan program ini hanya sebagai mesin pencetak invoice saja, sesuai dengan permintaan teman saya, jadi tidak menyimpan hasil transaksi invoice. Semoga Bermanfaat bagi rekan - rekan.
tools :
Microsoft Visual Basic 6.0
Microsoft Excel 2007
Labels: EXCEL, microsoft, SOFTWARE, TIPS, Visual Basic
