Loop problem

F

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: 7

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
What is missing is the Word file...

Try changing the macro as follows:
VBA Code: 
Sub FillBookmarksWithLoop()


' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer


' ouvrir la feuille Excel où sont les données
Set ws = ThisWorkbook.Sheets("Data")

' ouvrir Word
Set objWord = CreateObject("Word.Application")

objWord.Visible = True




For i = 2 To 3
    ' ouvrir le template
    objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
    
    With objWord.ActiveDocument
    
        ' 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)
        
        'etc
        'etc
        
        ' sauver le fichier Word
        .SaveAs ("C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\" & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -6 Scope_Statement.docx")
        .Close False
    End With
Next

objWord.Quit
Set objWord = Nothing
End Sub
 
Upvote 0
Anthony47 said:
What is missing is the Word file...

Try changing the macro as follows:
VBA Code: 
Sub FillBookmarksWithLoop()


' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer


' ouvrir la feuille Excel où sont les données
Set ws = ThisWorkbook.Sheets("Data")

' ouvrir Word
Set objWord = CreateObject("Word.Application")

objWord.Visible = True




For i = 2 To 3
    ' ouvrir le template
    objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
   
    With objWord.ActiveDocument
   
        ' 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)
       
        'etc
        'etc
       
        ' sauver le fichier Word
        .SaveAs ("C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\" & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -6 Scope_Statement.docx")
        .Close False
    End With
Next

objWord.Quit
Set objWord = Nothing
End Sub
Click to expand...
Thanks Anthony

Since my previous message I've made some progress

Now the loop works correctly.
But if I run the macro twice, I have an error message saying "Word cannot save this file because it is already open elsewhere"

When I try to delete the Word document i have a message saying " The action can't be completed because the file is open in Word", but Word is closed

So far I don't know how to fix this

Any clue ?

Thanks
 
Upvote 0
Which is your current code and on which line do you get the error?
 
Upvote 0
My current code is


VBA Code: 
Sub FillBookmarksWithLoop()


' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer


  ' 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", ReadOnly:=True ' change as required
 
 'ActiveDocument.Unprotect


  ' boucle de la ligne 2 à la ligne 150
  For i = 2 To 150
 
    ' tester si la Demand_ref est vide
    If IsEmpty(ws.Cells(i, 2)) = True Then
 
            ' fermer le fichier Word
            objWord.Visible = False
            Set objWord = Nothing
          
            ' arrêter la macro
            Exit Sub
           
            Else
 
 
        With objWord.ActiveDocument

            ' 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, 4) & " - " & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -9 Scope_Statement.docx")
           
            ' fermer le fichier Word
            objWord.Visible = False
            Set objWord = Nothing
           
           
            ' 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", ReadOnly:=True  ' change as required
     
 
'With objWord.ActiveDocument

        End With
       
  End If
 
  Next
 
 
  ' fermer Word
 
  Set objWord = Nothing
  objWord.Quit
  Set objWord = Nothing
 
End Sub


The error message when I run the macro twice is "Word cannot save this file because it is already open elsewhere"

When I try to delete the Word document manually I have a message saying " The action can't be completed because the file is open in Word", but Word is closed.
 
Last edited by a moderator:
Upvote 0
@fabcat1
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. This time I have added them for you in the previous post. 😊
 
Upvote 0
Your sequence is confused and indeed you leave the template open and many open but hidden Word Application sessions

Try this version, that includes the suggestions I gave in my previuou message:
VBA Code: 
Sub FillBookmarksWithLoop()


' définir les objets
Dim objWord As Object                    ''Word.Application  if lib is referenced
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer


' 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      !!!! NOT HERE
'''' objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx", ReadOnly:=True ' change as required

'ActiveDocument.Unprotect


' boucle de la ligne 2 à la ligne 150
For i = 2 To 150
    ' tester si la Demand_ref est vide
    If IsEmpty(ws.Cells(i, 2)) = True Then
        ' fermer le fichier Word
'''        objWord.Visible = False    !!! NO
        On Error Resume Next
            objWord.ActiveDocument.Close False
            objWord.Quit
            Set objWord = Nothing
        On Error GoTo 0
        ' arrêter la macro
        Exit Sub
    Else
        objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx", ReadOnly:=True ' change as required
        With objWord.ActiveDocument
            ' 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, 4) & " - " & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -9 Scope_Statement.docx")
            .Close False
           
            ' fermer le fichier Word !!! THESE LINES ONLY HIDE, they don't close
'''            objWord.Visible = False
'''            Set objWord = Nothing
           
           
            ' ouvrir Word         !!! OBJWORD IS STILL LIVE
'''            Set objWord = CreateObject("Word.Application")
'''            objWord.Visible = True
           
            ' ouvrir le template      ''' NOT HERE
'''            objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx", ReadOnly:=True ' change as required
           
           
            'With objWord.ActiveDocument
           
        End With
   
    End If

Next


' fermer Word
'''Set objWord = Nothing
'''objWord.Quit
'''Set objWord = Nothing

On Error Resume Next
    objWord.ActiveDocument.Close False
    objWord.Quit
    Set objWord = Nothing
On Error GoTo 0


End Sub
I left several lines of your code but commented them adding information on why those lines are incorrect; you can remove them to have a neat code
 
Upvote 0
Solution
if this is on the first cycle (ie I = 2), then I think it's better to reboot your pc before trying
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Locked
  • Question
Can you please help me for Excel to Word Macro
Replies
5
Views
739
Fluff
Fluff
A
  • Question
VBA to copy data in Excel and export to Word Document
Replies
1
Views
555
sc0001
S
K
  • Question
How to prompt users to select file path
Replies
4
Views
879
krprime90
K
S
  • Question
VBA returning different results than expected IF GOTO ELSE
Replies
4
Views
575
Sethomas5
S
S
  • Question
Open Word Template, change, save, close and open again
Replies
2
Views
365
Sethomas5
S

Forum statistics

Threads
1,216,219
Messages
6,129,575
Members
449,519
Latest member
Rory Calhoun

Share this page

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