Loop problem

fabcat1

New Member
Joined
May 20, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • moulinette.png
    moulinette.png
    94.6 KB · Views: 8
Thank you for the feedback
(but look at how the loop is organized and match it with your initial approach)
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Problem fixed

This thread can be closed
I switched the marked solution with the post that answered this question.

@fabcat1 - when the problem is fixed, or in other words, your question is answered, please mark the post that answered your question instead of your final post to help future readers.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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
Back
Top