Excel to Word Automation

[jawaharprm]

Hi team,
i worked on a automation Excel to word, its working fine without error but only one data is not updating properly i enclosed the input word file and output word file and macro file to can say why only that particular data is not getting updated, pls help on same

this is input file


Output file

Sub Invoice_2_Click()

    Sheet2.Select
    Sheet2.Activate
    With ActiveSheet
    
        lastrow = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row
        
    End With
    
    For datarow = 2 To lastrow
    
            Call GenerateWordfunction(datarow)
    
    Next datarow
    
    MsgBox "File Conversion Successfully Completed....."

End Sub

Function GenerateWordfunction(datarow As Variant)

    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True
    
    Set wdoc = wApp.Documents.Open(ThisWorkbook.Path & "\Invoice 1.docx", ReadOnly = "True")

    With wdoc
                
        .Application.Selection.Find.Text = "@@Company1@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 1).Value
        .Application.Selection.EndOf
        
        .Application.Selection.Find.Text = "@@Accountno@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 3).Value
        .Application.Selection.EndOf
        
        .Application.Selection.Find.Text = "@@Company1@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 1).Value
        .Application.Selection.EndOf
      
        .Application.Selection.Find.Text = "@@Accountno@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 3).Value
        .Application.Selection.EndOf
        
        .Application.Selection.Find.Text = "@@Street@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 6).Value
        .Application.Selection.EndOf

        .Application.Selection.Find.Text = "@@Adress@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 7).Value
        .Application.Selection.EndOf

        .Application.Selection.Find.Text = "@@City@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 8).Value
        .Application.Selection.EndOf

        .Application.Selection.Find.Text = "@@ShipAcountno@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 9).Value
        .Application.Selection.EndOf

        .Application.Selection.Find.Text = "@@ShipName@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 10).Value
        .Application.Selection.EndOf
        
        .Application.Selection.Find.Text = "@@ShipStreet@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 12).Value
        .Application.Selection.EndOf
        
        .Application.Selection.Find.Text = "@@ShipAddress@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 13).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@ShipCity@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 14).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@BillAccountNo@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 15).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@BPName@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 16).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@BPStreet@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 18).Value
        .Application.Selection.EndOf
                                
        .Application.Selection.Find.Text = "@@BPAdress@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 19).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@BPCity@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 20).Value
        .Application.Selection.EndOf
                        
        .Application.Selection.Find.Text = "@@ContactName@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 21).Value
        .Application.Selection.EndOf
                                
        .Application.Selection.Find.Text = "@@Email@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 22).Value
        .Application.Selection.EndOf
                                
        .Application.Selection.Find.Text = "@@ContactNo@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(2).Cells(datarow, 23).Value
        .Application.Selection.EndOf

              
        .SaveAs Filename:=ThisWorkbook.Path & "\" & Sheets(2).Cells(datarow, 1).Value & "_" & Sheets(2).Cells(datarow, 2).Value & ".docx"
        
    End With
    
    wdoc.Activate

    Set wdoc = Nothing
    
    wApp.Quit
        
End Function

 

[Monty]

Hello

Its great piece of code,

I think you should double-check the placeholder names (@@Company1@@, @@Accountno@@, etc)

 

[jawaharprm]

Hi Thanks for your time, i checked many times for ex - @@company1@@ i used double time it works, but only issues is on account, for that i copied that account no and pasted in last row and changed the name too, but the same issues.

       .Application.Selection.Find.Text = "@@SoldAccno@@"
        .Application.Selection.Find.Execute
        .Application.Selection = Sheets(1).Cells(datarow, 24).Value
        .Application.Selection.EndOf

 

[Marc L]

Hi, rather than replacing text you should better use Word basics like Bookmarks​

as explained in Word help like in Word VBA help as well …​

​

 

[vletm]

caspianzephyr​

You should open a new thread for You own case.
(as You've read from Forum Rules).
This thread belongs to someone else.