Visual Basic with Ms Excel as Database

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