[VBA] Pasting data in Word looping for no apparent reason

[rcastilho91]

I'm creating an Excel document that, among other things, copies a few arrays of data into a Word file.

Everything is working absolutely fine except that, when it pastes the selection I'm interested in, it pastes it infinitely until I intervene by taking down Word via Task Manager.

There is no reason, as far as I can see, for it to loop. So I'm pretty clueless of what to do next, and I was hoping you guys would shed a light into what I'm doing wrong. The concept is as follows:


Sub TestA()

'Word objects.
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdbmRange As Word.Range

'Excel objects.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnReport As Range

'Opening app and document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Users\RCO1\Desktop\Teste VBA\2. Conceptual Testing\Export\XLWDTST.docx")
Set wdbmRange = wdDoc.Bookmarks("TableInsertion").Range

'Selecting array
Sheets("ExportMe").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'Pasting data
With wdbmRange
.PasteSpecial '<------- it simply goes back up to the beginning of the code at this point
CutCopyMode = False
End With

'Closing and saving
wdDoc.Save
wdDoc.Close
wdApp.Quit

Set wdApp = Nothing
Set wdDoc = Nothing


End Sub


Just note that I can't simply transform this array into a table so to speak for formatting reasons.

Thanks in advance.

[Dave]

HTH. Dave

Dim ORng as Object
With wdDoc
Set ORng = WdDoc.Bookmarks("TableInsertion").Range
ORng.Delete
ORng.Paste
.Bookmarks.Add "TableInsertion", ORng
End With
Application.CutCopyMode = False
Set ORng = Nothing

[snb]

This code suffices:


Sub M_snb()
Sheets("ExportMe").cells(1).currentregion.copy

with getobject("C:\Users\RCO1\Desktop\Teste VBA\2. Conceptual Testing\Export\XLWDTST.docx")
.Bookmarks("TableInsertion").Range.PasteExcelTable 0, 0, 0
.close -1
end with
End Sub

Please learn a language (in thei cas VBA) before you use it.
Don't use VBA-code you do not understand.

[rcastilho91]

Please learn a language (in thei cas VBA) before you use it.
Don't use VBA-code you do not understand.

Well, it gets quite hard to learn a language without using it. I have studied it for years until 2006, although my line of work never required me to use it again until now. And, like I said, it's all part of a much bigger code, this is the one part I'm stuck in.

I appreciate the help, but there's no need to be rude about it.

[rcastilho91]

HTH. Dave

Dim ORng as Object
With wdDoc
Set ORng = WdDoc.Bookmarks("TableInsertion").Range
ORng.Delete
ORng.Paste
.Bookmarks.Add "TableInsertion", ORng
End With
Application.CutCopyMode = False
Set ORng = Nothing


Thanks, Dave. This code does not solve it, though. This piece is clearing up the bookmark. My issue is, I'm able to properly paste the selection into Word, but it goes into a loop I cannot get out of. It works when I transform the object into table but, then again, transforming it into a table screws up the required formatting.

I'd just like to know if there's a way I can paste the selection just as it's happening right now, but without the loop I don't know where is coming from.

Thanks.

[Dave]

I think U could get rid of the selection stuff with..

Sheets("ExportMe").UsedRange.Copy
It looks like from A1 your copying all the cells to the last row and last column... the used range. The use of selection is usually a bad thing. So are U sure that the relevant range to copy and paste actually fits in the document? Maybe word is trying to format the paste? There is nothing in the code that would loop. I suggest trying a smaller range area for testing purpses and see what happens. Dave

[rcastilho91]

I think U could get rid of the selection stuff with..

Sheets("ExportMe").UsedRange.Copy
It looks like from A1 your copying all the cells to the last row and last column... the used range. The use of selection is usually a bad thing. So are U sure that the relevant range to copy and paste actually fits in the document? Maybe word is trying to format the paste? There is nothing in the code that would loop. I suggest trying a smaller range area for testing purpses and see what happens. Dave

Good advice, I'll see that. Unfortunately, though, it has to be dynamic. I never know if there's going to be a single line or a thousand.

[snb]

Test the code in #3.

[rcastilho91]

Test the code in #3.

It worked. As I looked at it, I thought you'd have to set the whole region as a table first. :wot

That was a life saver, man. Thanks a lot.