I'm creating a macro to generate Word documents and fill them with the content of an Excel table using bookmarks.
So far the macro successfully fill the first file, fill the bookmarks with the content of the second row.
It then create the second file but don't fill the bookmarks with the content of the third row.
I have the following error message " the requested member of the collection does not exists"
when I check i = 3
I have the feelong the loop is not set up correctly but I can't find out how to fix this
Thanks for your help
Here is my code :
Sub FillBookmarksWithLoop()
' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer
'for i = 2 to lastrow
' ouvrir la feuille Excel où sont les données
Set ws = ThisWorkbook.Sheets("Data")
' ouvrir Word
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
With objWord.ActiveDocument
For i = 2 To 3
' copier dans le bookmark correspondant le contenu de la cellule
.Bookmarks("Site_Code").Range.Text = ws.Cells(i, 2)
.Bookmarks("Site_Name").Range.Text = ws.Cells(i, 3)
.Bookmarks("Demand_ref").Range.Text = ws.Cells(i, 4)
.Bookmarks("Project_Title").Range.Text = ws.Cells(i, 5)
.Bookmarks("Localisation").Range.Text = ws.Cells(i, 6)
.Bookmarks("Requestor").Range.Text = ws.Cells(i, 7)
.Bookmarks("Program_Manager").Range.Text = ws.Cells(i, 8)
.Bookmarks("SAP_Code").Range.Text = ws.Cells(i, 9)
.Bookmarks("Request_date").Range.Text = ws.Cells(i, 10)
.Bookmarks("Target_date_Study").Range.Text = ws.Cells(i, 11)
.Bookmarks("Target_date_Build").Range.Text = ws.Cells(i, 12)
.Bookmarks("Project_Description").Range.Text = ws.Cells(i, 13)
.Bookmarks("Objectives_and_deliverables").Range.Text = ws.Cells(i, 14)
.Bookmarks("Out_of_scope").Range.Text = ws.Cells(i, 15)
.Bookmarks("Assumptions").Range.Text = ws.Cells(i, 16)
.Bookmarks("Budget_Material").Range.Text = ws.Cells(i, 17)
.Bookmarks("Budget_Partner").Range.Text = ws.Cells(i, 18)
.Bookmarks("Budget_Internal_Ressources").Range.Text = ws.Cells(i, 19)
.Bookmarks("Budget_Total").Range.Text = ws.Cells(i, 20)
' sauver le fichier Word
.SaveAs ("C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\" & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -6 Scope_Statement.docx")
' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
Next
'With objWord.ActiveDocument
End With
End Sub
So far the macro successfully fill the first file, fill the bookmarks with the content of the second row.
It then create the second file but don't fill the bookmarks with the content of the third row.
I have the following error message " the requested member of the collection does not exists"
when I check i = 3
I have the feelong the loop is not set up correctly but I can't find out how to fix this
Thanks for your help
Here is my code :
Sub FillBookmarksWithLoop()
' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer
'for i = 2 to lastrow
' ouvrir la feuille Excel où sont les données
Set ws = ThisWorkbook.Sheets("Data")
' ouvrir Word
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
With objWord.ActiveDocument
For i = 2 To 3
' copier dans le bookmark correspondant le contenu de la cellule
.Bookmarks("Site_Code").Range.Text = ws.Cells(i, 2)
.Bookmarks("Site_Name").Range.Text = ws.Cells(i, 3)
.Bookmarks("Demand_ref").Range.Text = ws.Cells(i, 4)
.Bookmarks("Project_Title").Range.Text = ws.Cells(i, 5)
.Bookmarks("Localisation").Range.Text = ws.Cells(i, 6)
.Bookmarks("Requestor").Range.Text = ws.Cells(i, 7)
.Bookmarks("Program_Manager").Range.Text = ws.Cells(i, 8)
.Bookmarks("SAP_Code").Range.Text = ws.Cells(i, 9)
.Bookmarks("Request_date").Range.Text = ws.Cells(i, 10)
.Bookmarks("Target_date_Study").Range.Text = ws.Cells(i, 11)
.Bookmarks("Target_date_Build").Range.Text = ws.Cells(i, 12)
.Bookmarks("Project_Description").Range.Text = ws.Cells(i, 13)
.Bookmarks("Objectives_and_deliverables").Range.Text = ws.Cells(i, 14)
.Bookmarks("Out_of_scope").Range.Text = ws.Cells(i, 15)
.Bookmarks("Assumptions").Range.Text = ws.Cells(i, 16)
.Bookmarks("Budget_Material").Range.Text = ws.Cells(i, 17)
.Bookmarks("Budget_Partner").Range.Text = ws.Cells(i, 18)
.Bookmarks("Budget_Internal_Ressources").Range.Text = ws.Cells(i, 19)
.Bookmarks("Budget_Total").Range.Text = ws.Cells(i, 20)
' sauver le fichier Word
.SaveAs ("C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\" & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -6 Scope_Statement.docx")
' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
Next
'With objWord.ActiveDocument
End With
End Sub