PDA

View Full Version : Pasting table from Excel 2013 to Word 2013 with VBA



simonlevey
06-19-2016, 03:52 AM
Hi all,

Although I have quite a bit of experience with Excel VBA I have little experience with Word VBA and have run into a bit of an odd issue.

I have the following table in Excel:

16427

I am using VBA across Excel and Word to build a report, in Word format, from one of my spreadsheets. Essentially I am copying this table into the clipboard and then attempting to paste it into a Word document.

If I paste it using the Word GUI, without VBA, it looks like this in Word. This is exactly what I want. As you can see, the spacing in the cells is maintained.

16428
If I record that action as a macro in Word I get the following:


Selection.PasteExcelTable False, False, False

Having looked this up I can see what it's doing and understand the parameters. The strange thing is, if I run the recorded Macro from Word or try to use the same code my Excel file the table pastes into the Word document but the formatting is all screwed up:

16429

It's as if Word has decided to insert a TAB between the cell wall and text for each of my cells and therefore shifted all of the text to the right. Although the top left cell is fine.

Hopefully someone has some ideas because i've hit a brick wall. It's probably also worth saying that I have tried the following with similar results although it works fine through the Word GUI:


Selection.PasteSpecial Link:=False, DataType:=wdPasteHTML, Placement:=wdInLine, DisplayAsIcon:=False

Please help as I don't really want to have to write code to go through each cell and delete the phantom additional characters.

Many Thanks

Simon

gmayor
06-19-2016, 08:54 PM
I can't reproduce this problem, using a mock up of your worksheet. Can you post the original worksheet and the document you have pasted into to see if anything obvious leaps out. I suspect it might have something to do with the styles used in your document.

simonlevey
06-20-2016, 02:11 AM
Hi,

Having done some more digging it looks like the Word document is causing the issue. If I try the same code with a new blank document it works just fine. I am currently in the process of building a new document to see if I can find the cause of the issue. Trying to find a formatting setting will be like looking for the proverbial needle so i'll try starting from scratch.

Will post an update shortly.

simonlevey
06-20-2016, 11:59 AM
Hi,

Having done some more digging it looks like the Word document is causing the issue. If I try the same code with a new blank document it works just fine. I am currently in the process of building a new document to see if I can find the cause of the issue. Trying to find a formatting setting will be like looking for the proverbial needle so i'll try starting from scratch.

Will post an update shortly.

I can now confirm that it must have been the original document causing the problem. I have now built up a new template document and it works fine.