W
Wullie
Hi,
I am looping through a spreadsheet and opening multiple versions of a word
template document.
When I run the macro withough stepping through, this opens all required
copies of the template except the last one. When I step through the code, it
opens all copies with no issues.
Has anyone come across this issue? Any idea how to resolve this?
Thanks
My code is as follows
For rowCounter = firstRow To lastRow Step 1
If UCase(Cells(rowCounter, 1).Value) = "TRUE" Then
' Update status bar progress message with printCounter number
Application.StatusBar = "Processing Initial Register " &
printCounter
' If column 1 'TRUE', set unitTitle variable as Unit Title of
row
unitCode = Cells(rowCounter, 2).Value & "/BC"
unitTitle = Cells(rowCounter, 4).Value
' Send commands to Word, now uses Word VBA command instead of
Excel VBA.
templateName = ThisWorkbook.Path &
"\Initial_Register_Template.dotx"
With wrdApp
' Create a new word document
Set wrdDoc = .Documents.Add(templateName, True,
wdNewBlankDocument, True)
' Set range as whole document
Set wrdRange = wrdDoc.Range
' Make word docs visible while updating
.Visible = True
With wrdRange.Tables(1).Range
With .Cells(2).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter lecturer
End With
With .Cells(8).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter deptCode
End With
With .Cells(10).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter courseName
End With
With .Cells(14).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter unitCode
End With
With .Cells(16).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter unitTitle
End With
With .Cells(20).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter startDate
End With
With .Cells(22).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter endDate
End With
With .Cells(28).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter days
End With
With .Cells(30).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter room
End With
With .Cells(37).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter startTime
End With
With .Cells(39).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter endTime
End With
End With
nextCell = 8
' Loop through each column of 'TRUE' row
For columnCounter = firstColumn To lastColumn Step 1
' Set name/DOB variables of column
candForename = Cells(7, columnCounter).Value
candSurname = Cells(8, columnCounter).Value
candDOB = Cells(9, columnCounter).Value
' If X in box, add name/DOB details to word document
If UCase(Cells(rowCounter, columnCounter)) = "X" Then
With wrdRange.Tables(2).Range
With .Cells(nextCell).Range
.InsertAfter candForename
End With
With .Cells(nextCell + 1).Range
.InsertAfter candSurname
End With
With .Cells(nextCell + 2).Range
.InsertAfter candDOB
End With
' Move table row to next row
nextCell = nextCell + 7
End With
End If
' Loop through columns
Next columnCounter
End With
' Update print counter to next number
printCounter = printCounter + 1
End If
' Loop to next row
Next rowCounter
I am looping through a spreadsheet and opening multiple versions of a word
template document.
When I run the macro withough stepping through, this opens all required
copies of the template except the last one. When I step through the code, it
opens all copies with no issues.
Has anyone come across this issue? Any idea how to resolve this?
Thanks
My code is as follows
For rowCounter = firstRow To lastRow Step 1
If UCase(Cells(rowCounter, 1).Value) = "TRUE" Then
' Update status bar progress message with printCounter number
Application.StatusBar = "Processing Initial Register " &
printCounter
' If column 1 'TRUE', set unitTitle variable as Unit Title of
row
unitCode = Cells(rowCounter, 2).Value & "/BC"
unitTitle = Cells(rowCounter, 4).Value
' Send commands to Word, now uses Word VBA command instead of
Excel VBA.
templateName = ThisWorkbook.Path &
"\Initial_Register_Template.dotx"
With wrdApp
' Create a new word document
Set wrdDoc = .Documents.Add(templateName, True,
wdNewBlankDocument, True)
' Set range as whole document
Set wrdRange = wrdDoc.Range
' Make word docs visible while updating
.Visible = True
With wrdRange.Tables(1).Range
With .Cells(2).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter lecturer
End With
With .Cells(8).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter deptCode
End With
With .Cells(10).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter courseName
End With
With .Cells(14).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter unitCode
End With
With .Cells(16).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter unitTitle
End With
With .Cells(20).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter startDate
End With
With .Cells(22).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter endDate
End With
With .Cells(28).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter days
End With
With .Cells(30).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter room
End With
With .Cells(37).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter startTime
End With
With .Cells(39).Range
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Bold = False
.Font.Name = "Arial"
.Font.Size = 11
.InsertAfter endTime
End With
End With
nextCell = 8
' Loop through each column of 'TRUE' row
For columnCounter = firstColumn To lastColumn Step 1
' Set name/DOB variables of column
candForename = Cells(7, columnCounter).Value
candSurname = Cells(8, columnCounter).Value
candDOB = Cells(9, columnCounter).Value
' If X in box, add name/DOB details to word document
If UCase(Cells(rowCounter, columnCounter)) = "X" Then
With wrdRange.Tables(2).Range
With .Cells(nextCell).Range
.InsertAfter candForename
End With
With .Cells(nextCell + 1).Range
.InsertAfter candSurname
End With
With .Cells(nextCell + 2).Range
.InsertAfter candDOB
End With
' Move table row to next row
nextCell = nextCell + 7
End With
End If
' Loop through columns
Next columnCounter
End With
' Update print counter to next number
printCounter = printCounter + 1
End If
' Loop to next row
Next rowCounter