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
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