EXCEL & WORD AUTOMATION (VB.NET)

Penjelasan Program Aplikasi

Contoh program yang dibuat adalah Program nilai Mahasiswa. Sebagai contohnya dalam program ini kita akan mengimput :

1. Nama
2. Nilai Kuis 1
3. Nilai UTS
4. Nilai Kuis 2
5. Nilai Tugas
6. Nilai UAS
7. Total Nilai
8. Keterangan
9. Indeks Prestasi

Bila semua data sudah diinput dan kita klik 'Surat Pemberitahuan' , maka data tersebut akan tersimpan dalam format MS. Word dalam format surat. Selain itu,bila kita klik 'Daftar Nilai', maka data tersebut akan ditampilkan dalam format Excel. Bila kita ingin mengimput data baru kita bisa klik 'Data Baru'. Akhirnya, bila semua data sudah kita input maka kita bisa keluar dari program tersebut dengan mengklik 'Keluar'

Source Coding

Imports Microsoft.Office.Interop
Imports word = Microsoft.Office.Interop.Word

Public Class Form1

    Dim app As New Excel.Application
    Dim book As Excel.Workbook
    Dim row As Long
    Dim sheet As Excel.Worksheet
    Private Sub btnkeluar_Click(sender As Object, e As EventArgs) Handles btnkeluar.Click
        Me.Close()
    End Sub

    Private Sub btndatabaru_Click(sender As Object, e As EventArgs) Handles btndatabaru.Click
        Me.txtnama.Text = ""
        Me.txtkuis1.Text = ""
        Me.txtuts.Text = ""
        Me.txtkuis2.Text = ""
        Me.txttugas.Text = ""
        Me.txtuas.Text = ""
        Me.txttotalnilai.Text = ""
        Me.txtketerangan.Text = ""
        Me.txtindeks.Text = ""
        Me.txtnama.Focus()
    End Sub

    Private Sub btnsurat_Click(sender As Object, e As EventArgs) Handles btnsurat.Click
        Dim app As New word.Application
        Dim doc As New word.Document

        app.Visible = True
        doc = app.Documents.Open("F:\Tugas Pa Agus\SURAT PEMBERITAHUAN.docx")

        doc.Bookmarks("nama").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtnama.Text)

        doc.Bookmarks("kuis1").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtkuis1.Text)

        doc.Bookmarks("uts").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtuts.Text)

        doc.Bookmarks("kuis2").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtkuis2.Text)

        doc.Bookmarks("tugas").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txttugas.Text)

        doc.Bookmarks("uas").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtuas.Text)

        doc.Bookmarks("total").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txttotalnilai.Text)

        doc.Bookmarks("keterangan").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtketerangan.Text)

        doc.Bookmarks("indeks").Select()
        app.Selection.Font.Name = "Times New Roman"
        app.Selection.Font.Size = 12
        app.Selection.TypeText(txtindeks.Text)

        doc.SaveAs("F:\Tugas Pa Agus\Word1.docx")
        app.Quit()
    End Sub

    Private Sub btndaftarnilai_Click(sender As Object, e As EventArgs) Handles btndaftarnilai.Click
        app.Visible = True
        book = app.Workbooks.Open("F:\Tugas Pa Agus\Tabel Nilai.xlsx")
        sheet = book.Sheets("Sheet1")
        row = sheet.Range("A" & sheet.Rows.Count).End(Excel.XlDirection.xlUp).Row

        app.Range("A1").Value = "NO"
        app.Range("B1").Value = "Nama"
        app.Range("C1").Value = "Kuis 1"
        app.Range("D1").Value = "UTS"
        app.Range("E1").Value = "Kuis 2"
        app.Range("F1").Value = "Tugas"
        app.Range("G1").Value = "UAS"
        app.Range("H1").Value = "Total Nilai"
        app.Range("I1").Value = "Keterangan"
        app.Range("J1").Value = "Indeks Prestasi"

        app.Range("A" & row + 1).Value = CStr(row)
        app.Range("B" & row + 1).Value = txtnama.Text
        app.Range("C" & row + 1).Value = txtkuis1.Text
        app.Range("D" & row + 1).Value = txtuts.Text()
        app.Range("E" & row + 1).Value = txtkuis2.Text
        app.Range("F" & row + 1).Value = txttugas.Text
        app.Range("G" & row + 1).Value = txtuas.Text
        app.Range("H" & row + 1).Value = txttotalnilai.Text
        app.Range("I" & row + 1).Value = txtketerangan.Text
        app.Range("J" & row + 1).Value = txtindeks.Text
        

        Me.txtnama.Text = ""
        Me.txtkuis1.Text = ""
        Me.txtuts.Text = ""
        Me.txtkuis2.Text = ""
        Me.txttugas.Text = ""
        Me.txtuas.Text = ""
        Me.txttotalnilai.Text = ""
        Me.txtketerangan.Text = ""
        Me.txtindeks.Text = ""

        book.SaveAs("F:\Tugas Pa Agus\Perubahan Tabel Nilai.xlsx")
        app.Quit()

    End Sub

    Private Sub btntotalnilai_Click(sender As Object, e As EventArgs) Handles btntotalnilai.Click
        txttotalnilai.Text = (Val(txtkuis1.Text) + Val(txtuts.Text) + Val(txtkuis2.Text) + Val(txttugas.Text) + Val(txtuas.Text)) / 5

        If (txttotalnilai.Text >= 85) Then
            txtketerangan.Text = "A"
        ElseIf (txttotalnilai.Text >= 75) Then
            txtketerangan.Text = "B"
        ElseIf txttotalnilai.Text >= 60 Then
            txtketerangan.Text = "C"
        ElseIf txttotalnilai.Text >= 55 Then
            txtketerangan.Text = "D"
        Else : txtketerangan.Text = "E"
        End If

        If (txtketerangan.Text = "A") Then
            txtindeks.Text = "Lulus"
        ElseIf (txtketerangan.Text = "B") Then
            txtindeks.Text = "Lulus"
        ElseIf (txtketerangan.Text = "C") Then
            txtindeks.Text = "Lulus"
        Else : txtindeks.Text = "Tidak Lulus"
        End If

    End Sub

End Class

Screen Shoot

Model Program 

Program Dijalankan

Format Word

Format Excel