jawaharprm
Member
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
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
Code:
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