Inserting an Excel Table into Word

Topic

New Reply

I had gotten into the habit of inserting tables from Excel by copying them, and pasting them without formatting into Word. In fact, a lounger gave me a macro that cleaned up a few glitches.

Typically the table was wider than the paper. In 2003 it hung over the page width, but using the “normal” document setting, I could see the extra parts. That enabled me to adjust the table to properly fit the page, deleting columns as necessary, and rearranging others.

2007 seems to automatically squeeze everything onto the page width. Is there any way that I can return to the previous behavior?

Reply | Quote

Replies

When I test this using Word and Excel 2007, it seems to work as it did previously – the table comes into the Word document at full width, with table content that is wider than the page, hanging off the right side of the page.

Are you using ordinary Copy and Paste commands?

Gary

Reply | Quote

Are you using ordinary Copy and Paste commands?

Good Question. I don’t use Copy and Paste directly; another lounger gave me a macro that inserted it with a few additions:

Sub PasteXLTable()
On Error GoTo ExitHere
Selection.PasteExcelTable False, True, False
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.Tables(1).Rows.HeightRule = wdRowHeightAuto
Selection.Tables(1).Rows.AllowBreakAcrossPages = False
ExitHere:
End Sub

(2003 insisted on fixing the row height which was often larger than it should be for the subset that I actually included in the document; it also allowed a row to break across a cell, something that I could not allow for my reports. That is the reason for the last two commands.)

If this macro needs tweaking, I would appreciate it. I am just starting to get to speed on 2007.

Reply | Quote

If you want to keep using that macro, one thing you could try would be to change this line:

Selection.PasteExcelTable False, True, False

to:

Selection.PasteExcelTable False, False, False

– that middle “True” tells the code to apply Word formatting to the pasted in table – and that appears to be forcing the table to fit in the page width.

Changing it to “False” allows the table to come in at full width. The downside is that you’ll then have to apply more formatting to the table, either manually, or by adding to the macro.

Just a note: I’m going to be offline for the next day and a half (heading to the beach!), so won’t be able to follow this thread for a couple of days.

Gary

Reply | Quote

Just a note: I’m going to be offline for the next day and a half (heading to the beach!), so won’t be able to follow this thread for a couple of days.

Thanks. I will try your suggestion.

Meanwhile, I hope you enjoy the beach. I just got back from camping, hiking, canoeing, and biking with some Boy Scouts.

Reply | Quote

If you want to keep using that macro, one thing you could try would be to change this line:

Selection.PasteExcelTable False, True, False

to:

Selection.PasteExcelTable False, False, False

– that middle “True” tells the code to apply Word formatting to the pasted in table – and that appears to be forcing the table to fit in the page width.

Changing it to “False” allows the table to come in at full width. The downside is that you’ll then have to apply more formatting to the table, either manually, or by adding to the macro.

Well, I finally got to try this. And, it worked. It does insert the table, and does not force the table to remain within the margins. And the other items mentioned also work: The row height is not fixed, and the cells do not break at the page.

Thank you.

Cheers

There is still one other problem that this macro has. In 2003, I had the default table come in with the top row repeating as a header. In the 2003, I had played with the default table, making the top row repeat as a header. Is it possible to insert this as a command into the macro?

And, where can I find a list of the macro commands, perhaps so I can modify things myself?
,

Reply | Quote

That was a looong camping trip!

Seriously, glad that worked. To make the first row of the table repeat, you could add this line to macro (just before the “Exit Here”):

Selection.Tables(1).Rows(1).HeadingFormat = True

There’s no single perfect place to find the details of Word VBA macro commands. One way to get info is click on a piece of the code that you’re interested in (for instance in the above code, click on “HeadingFormat”), and press F1 to bring up the Help content for that.

There are a number of books about Word macros that may organize things in a better way – I’m not familiar with any current Word programming books, but one that looks like a good place to start may be Word 2007 Macros & VBA Made Easy, by Guy Hart-Davis.

You can also get a lot of tips about Word macros at the Word MVP site.

Gary

Reply | Quote

You can also get a lot of tips about Word macros at the Word MVP site.

Gary

Thank you; I had totally forgotten about that site. And, it is in my “favorites” links also.

Reply | Quote