Berikut ini kode untuk baca-tulis file excel dengan vb express:
Module xls
Dim Koneksii As New System.Data.OleDb.OleDbConnection
Dim Perintahh As New System.Data.OleDb.OleDbCommand
Public Hasill As System.Data.OleDb.OleDbDataReader
Dim NotFreee As Boolean
Public Sub OpenGuard()
While NotFreee
Application.DoEvents()
End While
NotFreee = True
End Sub
Public Sub CloseGuard()
NotFreee = False
End Sub
Public Sub SetXLS(ByVal Alamat As String)
Koneksii.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & Alamat & “; Extended Properties=” & Chr(&H22) & “Excel 8.0;HDR=No;IMEX=2″ & Chr(&H22) & “;”
Perintahh.Connection = Koneksii
End Sub
Public Sub DoQueryXLS(ByVal Command As String)
Koneksii.Open()
Perintahh.CommandText = Command ‘”Select * from log”
Hasil = Perintahh.ExecuteReader
End Sub
Public Sub CloseKoneksi()
Koneksii.Close()
End Sub
Public Sub DoNonQueryXLS(ByVal Command As String)
Koneksii.Open()
Perintahh.CommandText = Command ‘”insert into”
Perintahh.ExecuteNonQuery()
Koneksii.Close()
End Sub
End Module
Berikut ini contoh penggunaannya:
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
xls.OpenGuard()
xls.SetXLS(“Presensi.xls”)
xls.DoNonQueryXLS(“Update [Sheet1$A1:A1] set F1=’123′”)
xls.DoNonQueryXLS(“Update [Sheet1$A2:A2] set F1=’WAS’”)
xls.DoNonQueryXLS(“Update [Sheet1$A3:A3] set F1=’TO’”)
xls.DoNonQueryXLS(“Update [Sheet1$B1:B1] set F1=’123b’”)
xls.DoNonQueryXLS(“Update [Sheet1$B2:B2] set F1=’WASb’”)
xls.DoNonQueryXLS(“Update [Sheet1$B3:B3] set F1=’TOb’”)
xls.DoQueryXLS(“select * from [Sheet1$A1:A2]“)
While Hasil.Read()
TextBox1.AppendText(Hasil(0) & vbCrLf)
End While
xls.CloseKoneksi()
xls.CloseGuard()
End Sub
