Word and Excel Automation - VB.NET

Word and Excel Automation

Aqila Qonita
1506715923
Akuntansi TSI A
Vokasi UI
Lab. Pemrograman Bisnis 2

Program Automasi Data Pasien

1. Penjelasan Program Aplikasi

Program yang saya buat adalah program automasi dari VB  ke MS. Word dan MS. Excel. Saya membuat form pada VB dengan judul Appointment Form. Pendaftar yang akan mengisikan data diri untuk membuat janji dengan dokter di RS Siloam Semanggi. Data-data yang harus diinput pada Form Pendaftar adalah:

Patient's Name
Patient's Gender
Patient's Medical Record Number
Docter's Speciality
Doctor's Name
Date and Time
Phone Number

Jika semua data sudah diinput, ketika kita meng-klik “Save".  maka semua data akan tersimpan di Ms.Excel. Jika kita ingin mencetak data tersebut, maka kita klik “Print Form” maka semua data akan meng-automasi dari VB.NET ke Ms.Word dan kita bisa langsung mencetaknya. Jika kita meng-klik “New Form”, maka semua data yang sudah kita tulis sebelumnya akan hilang dan kita bisa menulis lagi dari awal (Patient's Name). Jika kita meng-klik “Close”, maka form tersebut secara otomatis akan tertutup.

2. Screen Shoot

3. Source Code

Imports Microsoft.Office.Interop

Public Class form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        CBspec.Items.Add("Internist")

        CBspec.Items.Add("Neurologist")

        CBspec.Items.Add("Dentist")

        CBdoctor.Items.Add("Dr.Budi")

        CBdoctor.Items.Add("Dr.Gunawan")

    End Sub

    Private Sub Btnclose_Click(sender As Object, e As EventArgs) Handles Btnclose.Click

        Me.Close()

    End Sub

    Private Sub Btnsave_Click(sender As Object, e As EventArgs) Handles Btnsave.Click

        Dim app As New Excel.Application

        Dim book As Excel.Workbook

        Dim row As Long

        Dim sheet As Excel.Worksheet

        book = app.Workbooks.Open("C:\Users\LENOVO\Documents\semester 3\Book1.xlsx")

        sheet = book.Sheets("Sheet1")

        row = sheet.Range("A" & sheet.Rows.Count).End(Excel.XlDirection.xlUp).Row

        app.Range("A1").Value = "Patient's Name"

        app.Range("B1").Value = "Patient's Gender"

        app.Range("C1").Value = "Patient's Medical Record Number"

        app.Range("D1").Value = "Doctor's Speciality"

        app.Range("E1").Value = "Doctor's Name"

        app.Range("F1").Value = "Date and Time"

        app.Range("G1").Value = "Phone Number"

        app.Range("A" & row + 1).Value = txtname.Text

        If RBFemale.Checked Then

            app.Range("B" & row + 1).Value = RBFemale.Text

        End If

        If RBmale.Checked Then

            app.Range("E" & row + 1).Value = RBmale.Text

        End If

        app.Range("C" & row + 1).Value = txtnumber.Text

        app.Range("D" & row + 1).Value = CBspec.Text

        app.Range("E" & row + 1).Value = CBdoctor.Text

        app.Range("F" & row + 1).Value = DateTimePicker1.Text

        app.Range("G" & row + 1).Value = txtphone.Text

        book.Save()

    End Sub

    Private Sub Btnprint_Click(sender As Object, e As EventArgs) Handles Btnprint.Click

        Dim mywordapp As New Word.Application

        Dim myworddoc As New Word.Document

 myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("name").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(txtname.Text)

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("gender").Select()

        If RBFemale.Checked Then

            mywordapp.Selection.TypeText(RBFemale.Text)

        End If

        If RBmale.Checked Then

            mywordapp.Selection.TypeText(RBmale.Text)

        End If

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("number").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(txtnumber.Text)

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("Doctor_spec").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(CBspec.Text)

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("Doctor_name").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(CBdoctor.Text)

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("Date_time").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(DateTimePicker1.Text)

        myworddoc = mywordapp.Documents.Open("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        myworddoc.Bookmarks("Phone").Select()

        mywordapp.Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphJustify

        mywordapp.Selection.Font.Name = "Times New Roman"

        mywordapp.Selection.Font.Size = 14

        mywordapp.Selection.TypeText(txtphone.Text)

        myworddoc.SaveAs("C:\Users\LENOVO\Documents\semester 3\Data.docx")

        mywordapp.Visible = True

    End Sub

    Private Sub Btnnew_Click_1(sender As Object, e As EventArgs) Handles Btnnew.Click

        Me.txtname.Text = ""

        Me.txtnumber.Text = ""

        Me.CBspec.Text = ""

        Me.CBdoctor.Text = ""

        Me.DateTimePicker1.Text = ""

        Me.txtphone.Text = ""

        Me.txtname.Focus()

    End Sub

End Class

Thankyou :)