Excel-Vba macro to copy tables in word produces different formatting results

1
votes

I have created a test macro in excel that copies a range and pastes into word as a table while keeping the format. The results this macro produces are almost the same as normal copy and paste except for the positions of values inside the cells (they are all way too close to the top). Are there other ways to copy or transfer a table? Link for visual difference http://prntscr.com/oemp2d

I have tried the simple .Paste and also .PasteExcelTable (false,false,false) but both produce the same issue. I assume this is a problem of the "Range.Paste" but i also cant use .select in a word document from an excel vba.

Sub test22()

Set shet = ActiveSheet
Set wdDoc = ActiveDocument

shet.Range("A1:B4").Copy

wdDoc.Paragraphs(1).Range.Paste

End Sub

Table Differences

2
excelvbams-word
According to site guidelines, all information relevant to a question should be in the question, without relying on outside links. Even though your issue is resolved, Stack Overflow's concept is a Q&A repository where others can find information for their problems. The link to the image is not internal to SO, so may not be helpful for future visitors. Could you please use the edit link under the question to add the picture directly to the question? (It will use imgur.) Thank you :-) – Cindy Meister

2 Answers

3
votes

I reproduced your problem. It seems that it adds a space after each row in the table. So in Word proper, Home > Paragraph > Line and Paragraph Spacing > Remove Space After Paragraph seems to fix it.

In Word VBA I was able to do the same thing with:

ActiveDocument.Tables(activedocument.Tables.Count).Range.Paragraphs.SpaceAfter = False

So I think this will work:

Sub test22()
Set shet = ActiveSheet
Set wdDoc = ActiveDocument

shet.Range("A1:B4").Copy

wdDoc.Paragraphs(1).Range.Paste
wdDoc.Tables(activedocument.Tables.Count).Range.Paragraphs.SpaceAfter = False
End Sub

Note that I was copying a table in Excel and used the PasteExcelTable command. Weirdly Word doesn't let me select the entire Word table while I'm recording a macro, so I just fumbled around to get this line of code.

1
votes

The way to solve this is to remove spaceafter like it was suggested by Doug AND set linespacing to 1 (for those that have different linespacing defaults like me)

Sub test22()

Set shet = ActiveSheet
Set wdDoc = ActiveDocument

shet.Range("A1:B4").Copy

wdDoc.Paragraphs(1).Range.Paste
wdDoc.Tables(1).Range.Paragraphs.SpaceAfter = False
wdDoc.Tables(1).Range.ParagraphFormat.LineSpacing = LinesToPoints(1)
End Sub