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 :)
