Membuat Laporan dalam Excel dengan Visual Basic

Membuat Laporan dalam Excel dengan Visual Basic

Membuat Laporan dalam Excel dengan Visual Basic

Private Sub cmdexcel_Click()
Dim baru As New Excel.Application

With DE.rskaryawan

    If .State = 0 Then .Open
    .Filter = adFilterNone
    
    judul = "B2:" & Chr(.Fields.Count + 65) & "2" 'menentukan range
    header = "B4:" & Chr(.Fields.Count + 65) & "4"
    pilih = "B4:" & Chr(.Fields.Count + 65) & .RecordCount + 3
    
    baru.Workbooks.Add 'membuat lembar kerja baru
    baru.Worksheets(1).Activate
    
    baru.Worksheets(1).Cells(2, 2).Value = "Laporan Data Karyawan" & " (" & Format(Date, "dd-mmmm-yyyy") & ")"
    baru.Range(judul).Font.Size = 15 'mengisi judul
    baru.Range(judul).Merge
    
    For i = 1 To .Fields.Count 'menngisi header
        baru.Worksheets(1).Cells(4, i + 1).Value = .Fields(i - 1).Name
    Next
    
    baru.Range(header).Font.Bold = True
    baru.Range(header).Interior.ColorIndex = 50
    
    If .RecordCount > 0 Then .MoveFirst 'menampilkan data
    For i = 1 To .RecordCount
        For j = 1 To .Fields.Count
            baru.Worksheets(1).Cells(i + 3, j + 1).Value = .Fields(j - 1)
        Next
        .MoveNext
    Next
    
    baru.Range(pilih).Borders(xlEdgeTop).LineStyle = Solid 'pengaturan border
    baru.Range(pilih).Borders(xlEdgeTop).Weight = 2
    baru.Range(pilih).Borders(xlEdgeLeft).LineStyle = Solid
    baru.Range(pilih).Borders(xlEdgeLeft).Weight = 2
    baru.Range(pilih).Borders(xlEdgeRight).LineStyle = Solid
    baru.Range(pilih).Borders(xlEdgeRight).Weight = 2
    baru.Range(pilih).Borders(xlEdgeBottom).LineStyle = Solid
    baru.Range(pilih).Borders(xlEdgeBottom).Weight = 2
    baru.Range(pilih).Borders(xlInsideHorizontal).LineStyle = Solid
    baru.Range(pilih).Borders(xlInsideHorizontal).Weight = 2
    baru.Range(pilih).Borders(xlInsideVertical).LineStyle = Solid
    baru.Range(pilih).Borders(xlInsideVertical).Weight = 2
    
    baru.Visible = True 'menampilkan lembar kerja excel
    baru.Worksheets.PrintPreview
Sumber : https://filehippo.co.id/