Paste Excel Data into Word

jeffreybrown · #1 07-03-2022, 06:28 PM

I'm looking to take some data from Excel and paste it to Word as tables. So, with this Excel data there would end up being six separate tables in Word.

I've been working with a couple of threads with a possible solution, but just not getting the syntax right. This will just be written to a new word doc.

If found the following code and also the code from this most recent post.

This of course works great, but now to loop thru the ranges.

Code:

Sub Demo()
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    ActiveSheet.Range("A2").Resize(4, 2).Copy
    With wdApp
        .Visible = True
        Set wdDoc = .Documents.Add
        With wdDoc
            .Range.PasteExcelTable False, False, True
        End With
    End With
    Set wdDoc = Nothing: Set wdApp = Nothing
    Application.CutCopyMode = False
End Sub

Here is the code to loop thru the ranges, just not sure on how to combine the two.

Code:

Sub LoopthruRange()
   Dim Rng As Range
   With Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      For Each Rng In .Areas
         Rng.Resize(, 2).Copy
      Next Rng
   End With
End Sub

jeffreybrown · #2 07-07-2022, 03:33 PM

Any help here to nudge this in the right direction?

Guessed · #3 07-07-2022, 07:17 PM

You need to be more specific about where the breaks are and where the tables need to be placed in Word.

If you are putting the content into a blank Word document and the tables are just in order and defined by the empty first cell in a row then you could do a single paste and then split the table up.

Code:

Sub Demo()
  Dim wdApp As New Word.Application, wdDoc As Word.Document, wdTable As Word.Table
  Dim aRng As Range, i As Integer
  Set aRng = ActiveSheet.UsedRange
  aRng.Copy
  With wdApp
    .Visible = True
    Set wdDoc = .Documents.Add
    wdDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
    Set wdTable = wdDoc.Tables(1)
    For i = wdTable.Rows.Count To 1 Step -1
      If Len(wdTable.Rows(i).Range.Cells(1).Range.Text) < 3 Then
        wdTable.Rows(i).ConvertToText Separator:=vbCr
      End If
    Next i
  End With
  Set wdDoc = Nothing: Set wdApp = Nothing
  Application.CutCopyMode = False
End Sub

jeffreybrown · #4 07-08-2022, 09:59 AM

Thanks Andrew. With the tweak of one line, this works great. It turns out there were 11 paragraph marks being added instead of only one needed. After adding the merge, all is good.

Code:

If Len(wdTable.Rows(i).Range.Cells(1).Range.Text) < 3 Then
   wdTable.Cell(Row:=i, Column:=1).Merge MergeTo:=wdTable.Cell(Row:=i, Column:=11)
   wdTable.Rows(i).ConvertToText Separator:=vbCr
End If

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Auto populate form (data from excel) in Word based on drop down list selection (data from excel)	wvlls	Word VBA	1	03-22-2019 02:29 PM
Open Excel, copy & paste data & chart	Tal71	Word VBA	2	08-14-2018 08:28 PM
Paste data in "Accounting"format from Excel into Word changes formatting	cory_0101	Word	4	10-17-2012 12:30 PM
How to Copy data from Outlook mail and Paste it in a Excel sheet?	padhu1989	Outlook	0	09-11-2012 04:07 AM
Using Paste/Special wih Excel data	chickasaw	PowerPoint	0	02-05-2010 10:00 PM