#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
Any help here to nudge this in the right direction?
|
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
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 |
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 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 |