Issues w/ Copy/Pasting Excel Table->Word as Loop

[Oddball2020]

Thanks in advance for any assistance in helping correct this. I've been using this forum in my CVA reasearch for 6 yrs, and have always been able to figure out a solution from the excellent support it provides, but this is the first time I'm actually posting a question to it as dealing with Word from Excel VBA is a frustrating challenge for me.

I'm running Win 10 Enterprise and Excel & Word 2016.

I've made a VBA tool to that updates a table in an excel worksheet in a loop:
1) updating the table info from the source data worksheet (in a list w/ several columns) of rows
2) copying/pasting the table range into Word at the end paragraph
3) repeat on each iteration of the loop
4) saving/close the file as a doxc and pdf with a user defined file name (not included in my code below)

The issue I'm having is that though it works perfectly fine for the first 4 iterations in the loop, after the 5th iteration the pasting into Word gets wonky as the table format gets out of whack somehow. I find it odd that it's ok the first 4 times but not after, and there's obviously something I'm overlooking with the nusiences of dealing with Word from the Excel VBA side.

I know there's a better way than to finesse the margins in Word and the table range such that the paste into word causes the ending paragraph to be at the start/top of a new page, for the next iteration to paste to, and to force an autofit of the table into word and not cause the bottom of the table range to wrap onto the next page. I've even toyed with using bookmarks and named tables as other paste methods, but I suspect there's something else going on that I'm overlooking.

The Word docx settings are: T 0.5", B 0.21", L 0.5", R 0.23" with a footer... otherwise it's a blank page

Sub Save_To_Word()

Dim wkbD As Workbook
Set wkbD = ThisWorkbook

Dim folderPath As String
folderPath = Application.ThisWorkbook.Path

Application.DisplayAlerts = False

Sheets("Summary").Activate

Dim LastRowC As Long
LastRowC = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row ‘determines length of the data list from the length of Column B

Dim i As Long
Dim MemoTemplatefolder As String

Dim objWord As Word.Application
Dim objDoc As Word.Document

'Determine if Word application is already running…. Found that it was causing errors if so
If Not (objWord Is Nothing) Then
objWord.Quit (False)
Set objWord = Nothing
End If

MemoTemplatefolder = folderPath & "\Template.docx" 'where the Word template is located

Set objWord = CreateObject("Word.Application") 'open Word application
Set objDoc = objWord.Documents.Open(MemoTemplatefolder)
objWord.Visible = True

Dim TStart
TStart = Timer

Dim PC As Long

For i = 5 To LastRowC ‘the header section is on rows 1-4… data starts in row 5+

Sheets("CDRL Template").Cells(1, 16).Value = i ‘ the table cells automatically update from the offset/match formula to display the data according to the row # this corresponds to
Sheets("CDRL Template").Calculate ‘precaution to force excel to calculate the formulas in the table content

Sheets("CDRL Template").Activate
Range(Cells(1, 1), Cells(45, 13)).Copy ‘this is the range of the template table that is automatically updated after the Cell(1 ,16) is updated on each iteration… I’m not using a named range or Table

'START Loop to save memos
If i = 1 Then

objDoc.Paragraphs(1).Range.PasteAndFormat wdFormatOriginalFormatting ‘thought forcing word to use the excel formatting would help in Word… don’t think it makes a difference

Application.CutCopyMode = False

Else 'after 1st iteration,

'objDoc.Range(objDoc.Characters.Count - 1).Paste ‘originally used to determine last paragraph

Do
DoEvents
Loop While (TStart + 1) > Timer ‘added this after I thought the macro was rushing too fast and pasting into previous paragraphs in Word; don't think it makes a difference

PC = objDoc.Paragraphs.Count 'Determines last paragraph, established after previous table paste; … given the range/size of the table, this always ends up 261 paragraphs from the previous in Word, at top of a new pg

objDoc.Paragraphs(PC).Range.PasteAndFormat wdFormatOriginalFormatting 'pastes to last paragraph

Application.CutCopyMode = False

End If
Next i

'Save/Close Word file: I removed this section of code for the forum post as there’s no issues with saving/closing the file as .doxc and .pdf

Set objDoc = Nothing
objWord.Quit
Set objWord = Nothing

Application.DisplayAlerts = True

End Sub

 

[shknbk2]

It's hard to know exactly what is going on with your data since I don't have either your Excel workbook or the Word template. However, I copied your code and made my own files in order to try out the basic functionality of your code. Without changing too much in the For loop (I commented out the Do loop), I found that copying the data into the last paragraph of Word always added the copied cells to the end of the previous cells. This made 1 long table based on the number of times the 45 rows were copied in each For loop. I hard coded the For loop at 8, so I ended up with a single table with 360 rows rather than 8 tables of 45 rows each.

I don't know if this is what you want. If it is, then my comments below won't be any help.

If you are trying to put 1 table on each page, what I did to make it work was to add another paragraph so there were 2 blank paragraphs at the end of the document. This way, when the last paragraph count is calculated and used to paste the next table, there is always a separating paragraph mark. This makes the tables separate objects. Just the portion I think you need to change is below (toward the end of the For loop):

End If
objDoc.Paragraphs.Add 'add paragraph so that 2 paragraphs are at the end of the document
                      'this way, when pasting the next section to the last paragraph, there is
                      'always 1 paragraph separating the two tables; this makes them distinct tables
Next i

'Delete the last double-paragraph that was added in the final loop
PC = objDoc.Paragraphs.Count
objDoc.Paragraphs(PC).Range.Delete

'Save/Close Word file: I removed this section of code for the forum post as there’s no issues with saving/closing the file as .doxc and .pdf

 

[Oddball2020]

It's hard to know exactly what is going on with your data since I don't have either your Excel workbook or the Word template. However, I copied your code and made my own files in order to try out the basic functionality of your code. Without changing too much in the For loop (I commented out the Do loop), I found that copying the data into the last paragraph of Word always added the copied cells to the end of the previous cells. This made 1 long table based on the number of times the 45 rows were copied in each For loop. I hard coded the For loop at 8, so I ended up with a single table with 360 rows rather than 8 tables of 45 rows each.

I don't know if this is what you want. If it is, then my comments below won't be any help.

If you are trying to put 1 table on each page, what I did to make it work was to add another paragraph so there were 2 blank paragraphs at the end of the document. This way, when the last paragraph count is calculated and used to paste the next table, there is always a separating paragraph mark. This makes the tables separate objects. Just the portion I think you need to change is below (toward the end of the For loop):

End If
objDoc.Paragraphs.Add 'add paragraph so that 2 paragraphs are at the end of the document
                      'this way, when pasting the next section to the last paragraph, there is
                      'always 1 paragraph separating the two tables; this makes them distinct tables
Next i

'Delete the last double-paragraph that was added in the final loop
PC = objDoc.Paragraphs.Count
objDoc.Paragraphs(PC).Range.Delete

'Save/Close Word file: I removed this section of code for the forum post as there’s no issues with saving/closing the file as .doxc and .pdf

Hey, that actually worked, big thanks! I new it had to be something "simple"... but it never would have occurred to me that all the pasting with no extra paragraph space was actually being dumped into the same table in Word. Having the extra paragraph did the trick, and all I needed to do was remove a row from my table in excel, and adjust the copy range from 45 to 44, and everything fit back nicely into a single word page! So relieved

 

[shknbk2]

Glad to help.

 

[smozgur]

Switched the solution post.
@Oddball2020 - Please mark the post that helped to solve the problem next time in order to help future readers.

 

[Oddball2020]

Switched the solution post.
@Oddball2020 - Please mark the post that helped to solve the problem next time in order to help future readers.

I did, the green check mark is against the 6:42PM post for shknbk2 above.

 

[smozgur]

I did, the green check mark is against the 6:42PM post for @shknbk2 above.

You initially selected your post, that's why I was alerted by the system and reviewed this question in the first place. And I switched the post as I explained. So, it is all done for this question. There is no problem you did that by the way, I am just reminding you about how we mark the posts as solutions to help future readers.

That would be great if you could do the same for your next questions, selecting the solution post instead of your feedback post. Thanks.