VBA Fill in Word Template and save as PDF

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
74
I'm looking for some help tweaking this code. I have data in an excel sheet and a Word template. It will fill in the template but when it goes to save it pops up and I have to manually save it. I want it to save and move on to the next line. Any help would be appreciated. The code I have so far is below.
Code:
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    FrDays = .Range("L3").Value 'Set From Days
    ToDays = .Range("O3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    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 'Make the application visible to the user
    End If
    
    
    LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
                DaysSince = .Range("o" & CustRow).Value
                If TemplName <> .Range("O" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 3 To 13 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
                                   .Range("o" & CustRow).Value = TemplName 'Template Name
                                   .Range("P" & CustRow).Value = Now
                                    If .Range("Q3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet1.Range("K" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet1.Range("F" & CustRow).Value & " We Miss You"
                                                      .Body = "Hello, " & Sheet1.Range("F" & CustRow).Value & " Its been a while since we have seen you so we wanted to send you a special letter. Please see the attached file"
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           
                                           WordDoc.Save
                                           WordDoc.Close
                                    End If
                        Kill (FileName) 'Deletes the PDF or Word that was just created
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
U can trial this line of code before the save...
Code:
WordApp.DisplayAlerts = False
HTH. Dave
 

KeithGargett

New Member
Joined
Sep 2, 2019
Messages
6
I have a similar Code within an excel workbook.

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=ActiveWorkbook.FullName & " - " & TheDate & " - " & TheAssemblyNumber & "-" & TheAssemblyName & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False[/FONT]
[Code\]
I think the trick is OpenAfterPublish

[QUOTE="floggingmolly, post: 5360047, member: 451874"]I'm looking for some help tweaking this code. I have data in an excel sheet and a Word template. It will fill in the template but when it goes to save it pops up and I have to manually save it.  I want it to save and move on to the next line.  Any help would be appreciated.  The code I have so far is below.
[code=rich]
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    FrDays = .Range("L3").Value 'Set From Days
    ToDays = .Range("O3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    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 'Make the application visible to the user
    End If
    
    
    LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
                DaysSince = .Range("o" & CustRow).Value
                If TemplName <> .Range("O" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 3 To 13 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
                                   .Range("o" & CustRow).Value = TemplName 'Template Name
                                   .Range("P" & CustRow).Value = Now
                                    If .Range("Q3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet1.Range("K" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet1.Range("F" & CustRow).Value & " We Miss You"
                                                      .Body = "Hello, " & Sheet1.Range("F" & CustRow).Value & " Its been a while since we have seen you so we wanted to send you a special letter. Please see the attached file"
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           
                                           WordDoc.Save
                                           WordDoc.Close
                                    End If
                        Kill (FileName) 'Deletes the PDF or Word that was just created
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
[/QUOTE]
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
74
I have a similar Code within an excel workbook.

Code:
[FONT=Verdana]ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=ActiveWorkbook.FullName & " - " & TheDate & " - " & TheAssemblyNumber & "-" & TheAssemblyName & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False[/FONT]
[Code\]
I think the trick is OpenAfterPublish[/QUOTE]


If I put the OpenAfterPublish line it gives an error. I've been fighting with this for days now. I can't get it to work.
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
74

ADVERTISEMENT

When it goes to save the PDF the box comes up to say where to save it and I have to click SAVE. I can't get it to just save without having to click the SAVE button
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
74
I tried this line of code and its still showing the save dialog box and I have to click SAVE.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,304
Messages
5,510,502
Members
408,792
Latest member
S_s_s

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top