Export Atau Cetak Data dari Database ke Excel menggunakan VB6
Thursday, August 11, 2016
Add Comment
Selamat sore pembaca,
1. Buat desain Form seperti dibawah ini :
2. Seperti biasa buat koneksi di Modul seperti gambar dibawah ini :
3. Buatlah fungsi untuk menampilkan data dari database
Function tampilData()
Dim xx As ListItem
Sql = "SELECT * FROM tblSiswa"
Set rs = New ADODB.Recordset
rs.Open Sql, con, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
With ListView1
.ListItems.Clear
.ColumnHeaders.Clear
.View = lvwReport
.FullRowSelect = True
.Gridlines = True
.ColumnHeaders.Add , , "NIS", 700
.ColumnHeaders.Add , , "NAMA SISWA", 4000
.ColumnHeaders.Add , , "KELAS", 1500
.ColumnHeaders.Add , , "NOMOR UJIAN", 3000
.ColumnHeaders.Add , , "RUANG", 2200
End With
While Not rs.EOF
Set xx = ListView1.ListItems.Add(, , rs("id"))
xx.SubItems(1) = IIf(IsNull(rs("namasiswa")), "-", rs("namasiswa"))
xx.SubItems(2) = IIf(IsNull(rs("kelas")), "-", rs("kelas"))
xx.SubItems(3) = IIf(IsNull(rs("noujian")), "-", rs("noujian"))
xx.SubItems(4) = IIf(IsNull(rs("ruang")), "-", rs("ruang"))
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Function4. Buat Fungsi untuk Export Data ke Excel
Function ExportToExcel()
Dim exc
Dim wb
Dim ws
Dim k As Integer
Dim CLms As Integer
Dim oExcel
Dim LstFld As Integer
On Error Resume Next
Set oExcel = CreateObject("Excel.Application")
Set wb = oExcel.Workbooks.Add
Set ws = wb.Worksheets(1)
Dim ws1 As Excel.Worksheet
Set ws1 = wb.Worksheets(3)
ws1.Delete
Set ws1 = Nothing
Set ws1 = wb.Worksheets(2)
ws1.Delete
Set ws1 = Nothing
ws.Cells.Clear
oExcel.Visible = True
ws.Cells(1, 1) = "ID NIS"
ws.Cells(1, 2) = "NAMA SISWA"
ws.Cells(1, 3) = "KELAS"
ws.Cells(1, 4) = "NOMOR UJIAN"
ws.Cells(1, 5) = "RUANG"
Dim flds As Integer
Dim flds2 As Integer
Dim strFlds As String
Dim SS As Integer
Dim myList As ListItem
Dim i As Integer
flds2 = LstFld - 1
For i = 1 To ListView1.ListItems.Count
Set myList = Me.ListView1.ListItems(i)
Dim strID As String
Dim strNama As String
Dim strKelas As String
Dim strNoUjian As String
Dim strRuang As String
strID = myList.Text
strNama = myList.SubItems(1)
strKelas = myList.SubItems(2)
strNoUjian = myList.SubItems(3)
strRuang = myList.SubItems(4)
ws.Range("A" & i + 1).Select
strFlds = ""
strFlds = strID
ws.Cells(i + 1, 1) = strFlds
strFlds = strNama
ws.Cells(i + 1, 2) = strFlds
strFlds = strKelas
ws.Cells(i + 1, 3) = strFlds
strFlds = strNoUjian
ws.Cells(i + 1, 4) = strFlds
strFlds = strRuang
ws.Cells(i + 1, 5) = strFlds
Next i
ws.Columns(1).AutoFit
ws.Columns(2).AutoFit
ws.Columns(3).AutoFit
ws.Columns(4).AutoFit
ws.Columns(5).AutoFit
ws.Name = "Data Siswa"
oExcel.Visible = True
Set wb = Nothing
Set ws = Nothing
Screen.MousePointer = vbDefault
Exit Function
1: MsgBox Err.Description, vbExclamation, Err.Number
Set wb = Nothing
Set ws = Nothing
Screen.MousePointer = vbDefault
Exit Function
End Function
5. Panggil Fungsi koneksi dan tampil data tersebut didalam Form Load
Private Sub Form_Load()
buka
tampilData
End Sub
6. Dan yang terakhir silahkan tambahkan fungsi ExportToExcel dalam event tombol Export To Excel
Private Sub Command1_Click()
ExportToExcel
End Sub
Setelah kita bahas bagaimana cara untuk impor data dari Excel kedalam database, kali ini saya akan bahas sebaliknya, yaitu bagaimana cara expor dari database ke Excel. Kasus seperti ini biasanya digunakan dalam mencetak sebuah laporan dalam bentuk excel. Dan sering sekali kita menemukan aplikasi yang menggunakan fasilitas ini untuk cetak ke Excel dan data yang di Excel diolah kembali oleh user. Oke langsung saja kita mulai langkah-langkahnya dibawah ini :
1. Buat desain Form seperti dibawah ini :
2. Seperti biasa buat koneksi di Modul seperti gambar dibawah ini :
3. Buatlah fungsi untuk menampilkan data dari database
Function tampilData()
Dim xx As ListItem
Sql = "SELECT * FROM tblSiswa"
Set rs = New ADODB.Recordset
rs.Open Sql, con, adOpenStatic, adLockOptimistic
If Not rs.EOF Then
With ListView1
.ListItems.Clear
.ColumnHeaders.Clear
.View = lvwReport
.FullRowSelect = True
.Gridlines = True
.ColumnHeaders.Add , , "NIS", 700
.ColumnHeaders.Add , , "NAMA SISWA", 4000
.ColumnHeaders.Add , , "KELAS", 1500
.ColumnHeaders.Add , , "NOMOR UJIAN", 3000
.ColumnHeaders.Add , , "RUANG", 2200
End With
While Not rs.EOF
Set xx = ListView1.ListItems.Add(, , rs("id"))
xx.SubItems(1) = IIf(IsNull(rs("namasiswa")), "-", rs("namasiswa"))
xx.SubItems(2) = IIf(IsNull(rs("kelas")), "-", rs("kelas"))
xx.SubItems(3) = IIf(IsNull(rs("noujian")), "-", rs("noujian"))
xx.SubItems(4) = IIf(IsNull(rs("ruang")), "-", rs("ruang"))
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Function4. Buat Fungsi untuk Export Data ke Excel
Function ExportToExcel()
Dim exc
Dim wb
Dim ws
Dim k As Integer
Dim CLms As Integer
Dim oExcel
Dim LstFld As Integer
On Error Resume Next
Set oExcel = CreateObject("Excel.Application")
Set wb = oExcel.Workbooks.Add
Set ws = wb.Worksheets(1)
Dim ws1 As Excel.Worksheet
Set ws1 = wb.Worksheets(3)
ws1.Delete
Set ws1 = Nothing
Set ws1 = wb.Worksheets(2)
ws1.Delete
Set ws1 = Nothing
ws.Cells.Clear
oExcel.Visible = True
ws.Cells(1, 1) = "ID NIS"
ws.Cells(1, 2) = "NAMA SISWA"
ws.Cells(1, 3) = "KELAS"
ws.Cells(1, 4) = "NOMOR UJIAN"
ws.Cells(1, 5) = "RUANG"
Dim flds As Integer
Dim flds2 As Integer
Dim strFlds As String
Dim SS As Integer
Dim myList As ListItem
Dim i As Integer
flds2 = LstFld - 1
For i = 1 To ListView1.ListItems.Count
Set myList = Me.ListView1.ListItems(i)
Dim strID As String
Dim strNama As String
Dim strKelas As String
Dim strNoUjian As String
Dim strRuang As String
strID = myList.Text
strNama = myList.SubItems(1)
strKelas = myList.SubItems(2)
strNoUjian = myList.SubItems(3)
strRuang = myList.SubItems(4)
ws.Range("A" & i + 1).Select
strFlds = ""
strFlds = strID
ws.Cells(i + 1, 1) = strFlds
strFlds = strNama
ws.Cells(i + 1, 2) = strFlds
strFlds = strKelas
ws.Cells(i + 1, 3) = strFlds
strFlds = strNoUjian
ws.Cells(i + 1, 4) = strFlds
strFlds = strRuang
ws.Cells(i + 1, 5) = strFlds
Next i
ws.Columns(1).AutoFit
ws.Columns(2).AutoFit
ws.Columns(3).AutoFit
ws.Columns(4).AutoFit
ws.Columns(5).AutoFit
ws.Name = "Data Siswa"
oExcel.Visible = True
Set wb = Nothing
Set ws = Nothing
Screen.MousePointer = vbDefault
Exit Function
1: MsgBox Err.Description, vbExclamation, Err.Number
Set wb = Nothing
Set ws = Nothing
Screen.MousePointer = vbDefault
Exit Function
End Function
5. Panggil Fungsi koneksi dan tampil data tersebut didalam Form Load
Private Sub Form_Load()
buka
tampilData
End Sub
6. Dan yang terakhir silahkan tambahkan fungsi ExportToExcel dalam event tombol Export To Excel
Private Sub Command1_Click()
ExportToExcel
End Sub
Selesai, coba jalankan projectnya dan pilih tombol export, maka akan membuka file Excel yang datanya sesuai data yang di Listview. Anda jangan khawatir jika terjadi error, karena akan saya sertakan file yang dapat anda pelajari dan anda ambil disini.
0 Response to "Export Atau Cetak Data dari Database ke Excel menggunakan VB6"
Post a Comment