Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2022, 06:28 PM
jeffreybrown jeffreybrown is offline Paste Excel Data into Word Windows 10 Paste Excel Data into Word Office 2016
Expert
Paste Excel Data into Word
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default Paste Excel Data into Word


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
Attached Files
File Type: xlsx Tables.xlsx (9.6 KB, 6 views)
Reply With Quote
  #2  
Old 07-07-2022, 03:33 PM
jeffreybrown jeffreybrown is offline Paste Excel Data into Word Windows 10 Paste Excel Data into Word Office 2016
Expert
Paste Excel Data into Word
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Any help here to nudge this in the right direction?
Reply With Quote
  #3  
Old 07-07-2022, 07:17 PM
Guessed's Avatar
Guessed Guessed is offline Paste Excel Data into Word Windows 10 Paste Excel Data into Word Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 07-08-2022, 09:59 AM
jeffreybrown jeffreybrown is offline Paste Excel Data into Word Windows 10 Paste Excel Data into Word Office 2016
Expert
Paste Excel Data into Word
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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 Excel Data into Word 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft