ALTERNATIF REPORT MENGGUNAKAN MS.EXCEL

ALTERNATIF REPORT MENGGUNAKAN MS.EXCEL
VISUAL BASIC 6.0


Buat Database seperti berikut. :

Kode_Barang Nama_barang UNIT QTY Harga
Text Text Text Int Currency
ISI RECORDNYA
CAL001 CAL BOX 20 8500
STR001 STATERKIT BOX 15 4500
XPK001 XLIM PACK BOX 10 3000
VGR001 VIGOR BOX 21 5500


Buat Tampilan Form seperti Dibawah Ini……..


Setting propertis untuk koneksi database, dll.

Mulai Diketik/…….

Dim klik_body As Object ' persiapkan untuk object Excel

Private Sub Command1_Click()
Dim i As Integer

'-------ambil data-------------------

Set klik_body = CreateObject("Excel.application") 'bikin object
klik_body.Visible = True ' excel muncul
klik_body.workbooks.Add 'nambahin workbook.
klik_body.sheets("sheet1").Name = "BARANG" ' me Rename Sheet barang
klik_body.sheets("sheet2").Name = "KEUANGAN" ' me Rename Sheet dua
klik_body.sheets("sheet3").Name = "STOK PRODUK" ' me Rename Sheet tiga
klik_body.sheets("barang").Activate 'Aktifkan Sheet barang


Data1.Recordset.MoveFirst
n = 1
While Not Data1.Recordset.EOF

With klik_body

.CELLS(1, 1).Value = "PT. BEGIN REPORT TO EXCEL"
.CELLS(2, 1).Value = "LAPORAN DATA KESELURUHAN BARANG " & UCase(Format(Month(Now), "MMMM")) & " TAHUN " & Year(Now)

.CELLS(1, 1).Font.COLORINDEX = 17 ' beri warna
.CELLS(2, 1).Font.COLORINDEX = 17


.CELLS(3, 1).Value = "No." 'Menulis di kolom 1 baris 1
.CELLS(3, 2).Value = "KODE BARANG" 'Menulis di Kolom 2 baris 1
.CELLS(3, 3).Value = "NAMA" ' Menulis di kolom 3 baris 1
.CELLS(3, 4).Value = "UNIT"
.CELLS(3, 5).Value = "QTY"
.CELLS(3, 6).Value = "HARGA"

'.
.Columns(1).columnwidth = 6 ' set kolom 1 lebarnya 6
.Columns(2).columnwidth = 15 'set kolom 2 lebarnya 20
.Columns(3).columnwidth = 25 'set kolom 3 lebarnya 20
.Columns(4).columnwidth = 8 'set kolom 4 lebarnya 20
.Columns(5).columnwidth = 8 'set kolom 3 lebarnya 20
.Columns(6).columnwidth = 10 'set kolom 4 lebarnya 2


.sheets("barang").RANGE("A1:f3").Font.Bold = True ' Bikin cetak tebel dari kolom A sampe C di sheet barang

.CELLS(3, 1).horizontalalignment = 3 '
.CELLS(3, 2).horizontalalignment = 3 'Bikin ketengah semua
.CELLS(3, 3).horizontalalignment = 3 '
.CELLS(3, 4).horizontalalignment = 3 '
.CELLS(3, 5).horizontalalignment = 3 '
.CELLS(3, 6).horizontalalignment = 3 '

For i = 1 To n

.CELLS(4 + n, 1).Value = i 'tulis no urut
.CELLS(4 + n, 2).Value = Data1.Recordset!kd_brg ' tulis nama
.CELLS(4 + n, 3).Value = Data1.Recordset!nm_brg ' tulis alamat
.CELLS(4 + n, 4).Value = Data1.Recordset!unit
.CELLS(4 + n, 5).Value = Data1.Recordset!qty
.CELLS(4 + n, 6).Value = Format(Data1.Recordset!harga, "#,###,###")

Next i

Ingat kembali Rumus DiExcel
.
CELLS(5 + n, 4).formula = Format("=count(E5:D" & 3 + i & ")", "#,###,###")

.CELLS(5 + n, 2).Value = "Jumlah Barang"

.CELLS(6 + n, 4).formula = Format("=MAX(E5:D" & 3 + i & ")", "#,###,###")

.CELLS(6 + n, 2).Value = "MAX"

.CELLS(7 + n, 4).formula = Format("=MIN(E5:D" & 3 + i & ")", "#,###,###")


.CELLS(7 + n, 2).Value = "MIN"


.sheets("barang").RANGE("a" & 3 + n & ":F" & 6 + i).Font.Bold = True 'cetak tebal bawahnya
.RANGE("a3:F" & 6 + i).borders.LineStyle = 1 ' buat kotak DAN ANGKA 5 ADALAH ROW

End With
Data1.Recordset.MoveNext
n = n + 1
Wend
Close (1) ' tutup file text

End Sub

Gambar tampilan Hasil Laporannya



Dicoba Aja OK.