Create Multiple Word Documents at Once (Excel VBA Macro Tutorial)

💥Subscribe: [ Ссылка ]

From Worksheet:

SERVICE AGREEMENT
This Service Agreement is made between
The Client agrees to pay
Both parties agree to the terms outlined in this Agreement
and will cooperate to ensure satisfactory completion of the services.

Code:

Sub batch_word_template()

Dim objWord As Object
Dim objDoc As Object
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim row_count As Integer
Dim i As Integer

Set ws1 = ThisWorkbook.Sheets("Agreement")
Set ws2 = ThisWorkbook.Sheets("Client List")

ws2.Activate
row_count = ws2.Cells(Rows.Count, 1).End(xlUp).row

For i = 2 To row_count

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Add
objDoc.SaveAs "C:\Users\greggowaffles\Documents\Youtube Videos\" & _
"Test\Service Agreements\Servive Agreement_" & _
ws2.Cells(i, 1).Text & "_" & ws2.Cells(i, 3).Text & ".docx"

With objWord
.Activate
.Selection.typetext ws1.Range("A1").Text
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typetext ws1.Range("A2").Text & " " & _
ws2.Cells(i, 1).Text & " from " & ws2.Cells(i, 2).Text & _
", and " & ws2.Cells(i, 3).Text & "."
.Selection.typeparagraph
.Selection.typetext ws1.Range("A3").Text & " " & _
ws2.Cells(i, 4).Text & "for these services."
.Selection.typeparagraph
.Selection.typetext ws1.Range("A4").Text & " " & _
ws1.Range("A5").Text
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typetext "Client Signature"
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typetext "_________________________________________"
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typetext "Service Provider Signature"
.Selection.typeparagraph
.Selection.typeparagraph
.Selection.typetext "_________________________________________"

End With

Next i

Set objDoc = Nothing
Set objWord = Nothing

End Sub

#excelvba #excelmacro #excelvbatutorial

свернуть