[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.

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