New with VBA

Ogedozie

New Member
Joined
May 1, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I'm having challenges identifying the errors on the code. Although, I got this code from a video tutorial I watched and I was able to create almost the exact Excel spreadsheet but it doesn't tend to execute the task of replacing the details in a word doc with that on the table.
I don't mind rewriting/using a different code that will work accurately.
Your assistance will be very much appreciated.



VBA Code:
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet3
 
  If .Range("Q2").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("E2").Select
    Exit Sub
  End If
    TemplRow = .Range("Q2").Value 'Set Template Row
    TemplName = .Range("E2").Value 'Set Template Name
    DocLoc = Sheet1.Range("E" & 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("A9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 5 To LastRow
                    Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                          For CustCol = 5 To 13 'Move Through 9 Columns
                                    TagName = .Cells(6, 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("D" & CustRow).Value & "_" & .Range("E" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
    
                                    If .Range("I2").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet3.Range("P" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet3.Range("E" & CustRow).Value
                                                      .Body = "Hello, " & Sheet3.Range("F" & CustRow).Value
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           WordDoc.PrintOut
                                           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
 

Attachments

  • 12801-74c013b5eb790cafdb8a186e7f5ec839.png
    12801-74c013b5eb790cafdb8a186e7f5ec839.png
    3.3 KB · Views: 2

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
359
Office Version
  1. 365
Platform
  1. Windows
Hi - I suspect you should be able to identify the cause of the problem if you comment out the following line: (meaning you should add a ' (quote) at the beginning of the line)

On Error Resume Next

Where the code would ordinarily break and tell y ou the source of the error, this line of code tells Excel to ignore it. Once you've done that, it should be easier to see why it's not working as expected. The only other thing that jumps out at me is that the code is only capturing data in columns 5 to 13, and rows 5 onwards. If your data doesn't sit in that range, I suspect it'll be ignored. I don't know if that's at all relevant - I can't see the image you uploaded, its too small on my computer.
 

Ogedozie

New Member
Joined
May 1, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Hi - I suspect you should be able to identify the cause of the problem if you comment out the following line: (meaning you should add a ' (quote) at the beginning of the line)

On Error Resume Next

Where the code would ordinarily break and tell y ou the source of the error, this line of code tells Excel to ignore it. Once you've done that, it should be easier to see why it's not working as expected. The only other thing that jumps out at me is that the code is only capturing data in columns 5 to 13, and rows 5 onwards. If your data doesn't sit in that range, I suspect it'll be ignored. I don't know if that's at all relevant - I can't see the image you uploaded, its too small on my computer.

Thank you Dan,
I've made the corrections and its working, though it seem not to replace all the targeted words in the Word Doc. What do i do?
Below is the image of the spread sheet.
 

Attachments

  • ex.png
    ex.png
    26.1 KB · Views: 3

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
359
Office Version
  1. 365
Platform
  1. Windows
Well, I think you're going to need to go through the code line by line, and work out where things are going wrong. If it's working for some of the targeted words but not others, then you'll need to work out why. I expect the that the problem is something in the following part of the code:

VBA Code:
For CustCol = 5 To 13 'Move Through 9 Columns
      TagName = .Cells(6, 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

Try and look for some guides on how to debug your code. This site is a great resource, and the blogger appears to have written some guidance about debugging. How to debug VBA? Debugging VBA in Excel - Analyst Cave I would suggest paying particular attention to the part about 'stepping through code'.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,953
Messages
5,621,799
Members
415,857
Latest member
braunReivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top