I'm still very new, but I'm making daily progress on my learning project.
I've been learning from videos how to do a mail merge with VBA, but I'm stuck on one aspect. The instructional videos I've found assume I want the merged form letters to be saved separately, like you would with letters to different people. But that's not what I need. I need all the separate pages to stay in one Word Document, like you would if you were making a detailed record of all of a company's equipment.... 50 (dynamic length) different pieces of equipment in an excel spreadsheet, 50 identical pages with specific data about each piece of equipment, 1 Word document.
I have some issues with the Word commands near the bottom, but I speculate it is because I need to change the approach because of a newer version of word than the video lesson I was learning from. Looking ahead though, I have no idea how to generate multiple records/pages in one word file... and that's what I'm hoping to learn from coming here.
Here's where I'm at so far for the mail merge module:
Option Explicit Sub CreateReportInWord() Dim DocLoc, TagName, TagValue As String Dim WordDoc, WordApp As Object Dim LastRow, DriveRow, DriveCol As Long With ActiveSheet DocLoc = Sheet1.Range("B3").Value On Error Resume Next 'If Word is already running Set WordApp = GetObject("Word.Application") If Err.Number <> 0 Then 'Launch a new instance of Word Err.Clear 'On Error GoTo Error Handler Set WordApp = CreateObject("Word.Application") WordApp.Visible = True End If Set WordDoc = WordApp.Documents.Open(Filename:=DocLoc, ReadOnly:=False) 'Open PM Template File LastRow = .Range("A200").End(xlUp).Row 'Determine last row in the table For DriveRow = 6 To LastRow For DriveCol = 1 To 10 'Increase this when columns are expanded TagName = .Cells(5, DriveCol).Value 'Tag Name TagValue = .Cells(DriveRow, DriveCol).Value 'Tag Value With WordDoc.Content.Replace .Text = TagName .Replacement.Text = TagValue .Wrap = wdFindContinue 'excel has issues with this line that I need to figure out .Execute Replace:=wdReplaceAll 'excel has issues with this line that I need to figure out End With Next DriveCol Next DriveRow End With End Sub