Pasting table from Excel 2013 to Word 2013 with VBA

[simonlevey]

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:

1.JPG

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.

2.JPG

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

Please Login or Register  to view this content.

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:

3.JPG

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:

Please Login or Register  to view this content.

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

[Norie]

Simon

Can you post the rest of the code?

Also, where are you running the code from?

[macropod]

Did you consider just using Selection.Paste, which is what pasting through the GUI defaults to?

[simonlevey]

Simon

Can you post the rest of the code?

Also, where are you running the code from?

Hi, thanks for replying.

In this example I have tried running the code from Excel and Word with the same results. I did however do the right thing and tried an empty Word file and it worked just fine. I have now rebuilt the Word file back into the template document and it's all working great. I am guessing that there was a setting or corruption in my document which was causing me problems.