Assalamualaikum Wr. Wb
Saya akan membuat word dan excel automation dari visual basic. Dalam tugas kali ini, saya akan membuat automation word yang menghasilkan output surat peminjaman fasilitas dan automation excel yang menghasilkan output data peminjaman fasilitas
Pertama, berikut design yang saya buat
Selanjutnya, berikut coding yang saya buat
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 sheet As Excel.Worksheet
Dim myWordApp As New word.Application
Dim myworddoc As New word.Document
Private Sub btnmulai_Click(sender As Object, e As EventArgs) Handles btnnew.Click
Me.txtsurat.Text = “”
Me.txtjabatan.Text = “”
Me.txttempat.Text = “”
Me.txtruang.Text = “”
Me.txttujuan.Text = “”
Me.txttanggal.Text = “”
Me.txtwaktu.Text = “”
Me.txtnama.Text = “”
Me.txtnpm.Text = “”
Me.txtjurusan.Text = “”
Me.txtalamat.Text = “”
Me.txttanggaal.Text = “”
Me.txtsurat.Focus()
End Sub
Private Sub btnexcel_Click(sender As Object, e As EventArgs) Handles btnexcel.Click
book = app.Workbooks.Open(“C:\Users\Alif\Documents\surat peminjaman fasilitas.xlsx”)
sheet = book.Sheets(“Sheet1”)
app.Range(“A1”).Value = “Ruang”
app.Range(“B1”).Value = “Tujuan”
app.Range(“C1”).Value = “Tanggal Peminjaman”
app.Range(“D1”).Value = “Waktu”
app.Range(“E1”).Value = “Nama”
app.Range(“F1”).Value = “NPM”
app.Range(“G1”).Value = “Jurusan”
app.Range(“H1”).Value = “Alamat”
app.Range(“A2”).Value = txtruang.Text
app.Range(“B2”).Value = txttujuan.Text
app.Range(“C2”).Value = txttanggal.Text
app.Range(“D2”).Value = txtwaktu.Text
app.Range(“E2”).Value = txtnama.Text
app.Range(“F2”).Value = txtnpm.Text
app.Range(“G2”).Value = txtjurusan.Text
app.Range(“H2”).Value = txtalamat.Text
book.Save()
app.Quit()
End Sub
Private Sub btnword_Click(sender As Object, e As EventArgs) Handles btnword.Click
Dim nWord As word.Application = CreateObject(“Word.application”)
Dim nDoc As word.Document
nWord.Visible = True
nDoc = nWord.Documents.Open(“C:\Users\Alif\Documents\surat peminjaman fasilitas.docx”)
nDoc.Bookmarks(“TUJUAN”).Select()
nWord.Selection.TypeText(txtsurat.Text)
nDoc.Bookmarks(“JABATAN”).Select()
nWord.Selection.TypeText(txtjabatan.Text)
nDoc.Bookmarks(“TEMPAT”).Select()
nWord.Selection.TypeText(txttempat.Text)
nDoc.Bookmarks(“RUANG”).Select()
nWord.Selection.TypeText(txtruang.Text)
nDoc.Bookmarks(“KEPERLUAN”).Select()
nWord.Selection.TypeText(txttujuan.Text)
nDoc.Bookmarks(“TANGGAL”).Select()
nWord.Selection.TypeText(txttanggal.Text)
nDoc.Bookmarks(“WAKTU”).Select()
nWord.Selection.TypeText(txtwaktu.Text)
nDoc.Bookmarks(“NAMA”).Select()
nWord.Selection.TypeText(txtnama.Text)
nDoc.Bookmarks(“NPM”).Select()
nWord.Selection.TypeText(txtnpm.Text)
nDoc.Bookmarks(“JURUSAN”).Select()
nWord.Selection.TypeText(txtjurusan.Text)
nDoc.Bookmarks(“ALAMAT”).Select()
nWord.Selection.TypeText(txtalamat.Text)
nDoc.Bookmarks(“TANGGAL2”).Select()
nWord.Selection.TypeText(txttanggaal.Text)
End Sub
Private Sub btnkeluar_Click(sender As Object, e As EventArgs) Handles btnexit.Click
Dim a As String
a = MsgBox(“Are You Sure?”, MsgBoxStyle.YesNo + MsgBoxStyle.Question, “Confirm”)
If a = vbYes Then
Me.Close()
End If
End Sub
End Class
Setelah itu, isi design yang sudah kita buat
Dan berikut output dari automation word
Selanjutnya, berikut output dari automation excel
Terima Kasih.