Solved: Excel tables pasted on different pages in Word

Hi everyone,
I'm just learning how to develop code, so I'm not sure that this can even be done. I need to take a table that will span several pages and paste it into word as a word table. However, the table title needs to be included on each page and cannot be a part of the table.

Thank you for any help you can give!

:confused:
I just reread my own post and realized that it's very confusing. Let's try again. I have a very long excel table that needs to be pasted into Word as a Word table (it will span several pages). The table titles in the word document need to be included on each page and cannot be a part of the table.
I am thinking that I need to write something to split the table in Excel then use bookmarks (?) or mailmerge (?) to create a document.

Thanks for your input!

Kathy:

I guess you are creating a boilerplate or something on the fly and you need to get the data from this Excel file (which changes over time).

If the number of rows and columns remains static and you just need to get the most recent data into your document, the best way would be to insert a picture of the Excel file as a link. So if the table spans three Word pages, you would insert 3 linked pictures or 3 pictures.

If the number of column and rows changes over time, then the best way might be to just port the raw data over into a huge word table. Then format the data and split the table accordingly over several pages.

I've written presentation systems that did just this kind of thing. It can be very tedious though. The problem is identifying all the parameters you need to address before you write the solution so you only have to do it once.

Having to go back and revisit code every few weeks (or even every few days) because someone changes the parameters isn't very fun :D

Hope this helps a little and gives you some direction.

Just hard to give you an exact answer of the best solution given the information on hand.

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

GW,
Thanks for the ideas. I'll try to explain the situation a little better. I have a client that has a document system that extracts data out of our report based on styles. Which for us means that the table title, "Table 1. Data" has to be style Header 3 and the table style set as Table. Subsequent pages of the table have to have "Table 1. Data Cont'd" as style Table contd. I tried pasting in the first table with the table title and then pasting in the rest of the table with a header row tagged as Table contd, and the word table set with repeat header row. Their system couldn't read it correctly. Currently, we split the table and add in the title manually which is not a problem if the tables are only a few pages. The problem is that some of the tables can be more than 3000 rows the manual entry is time consuming.
And of course, the tables change with each project :doh: .
Thank you again,
K

K:

Given the constraint that you can't use one large table and have Word break that table across pages with a repeat header row...

You need the code to paste in the table of data, break the table up for you, and insert the "Table 1. Data Cont'd" with the correct style.

Should be a breeze :D

Assuming each page in any of your reports has the same margin and page size you might try this:

A) Insert your table title and the whole table of data.

B) Go back to the top of your table.

C) Move down 1 wdLine

D) Start a WHILE loop to Test to see if you are still in a table. If you are not in a table, the the whole table is formatted and you are done. If you are still in the table continue to execute the following:

E) Determine where you are on the page using Selection.Information(wdVerticalPositionRelativeToPage). If you are very close to the bottom edge, then have the code split the table, insert the "Table cont'd" title with the appropriate style. Move down wdline. WEND back to the start of the while loop (step D) and continue.

Let me know if this helps in any way and/or if I missed something.

Enjoy!
g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

Thank you thank you thank you!!! :clap: That makes sense and seems like it will be a lot easier than what I was trying to do. I will let you know how I make out and post the code I come up with for everyone else.

Have a great holiday!!

:snowman:

K

G-,
I'm having some problems with determining if I'm still in a table. Either I'm not getting the code right (My first time working in Word) or I'm misunderstanding the Do While. Can you please expand on the Do While part of your suggestion?

Thanks,
K:dunno

Hi, sorry for the belated reply.

Here is the code:

Sub test()

While Selection.Information(wdWithInTable) = True
Selection.MoveDown (wdLine)
Wend

End Sub

Hope this helps,
g-

g-
Thanks for the help. I've gone a slightly different way with my code to make it more flexible. Here's what I've come up with. It works, but I'm sure it's sloppy and can use some help. I've set up my document to have the user enter the table title with the correct style on the first line,then the Continued table title with the correct style on the second line and then insert the table. I have the user selecting any cell in the header row (in case there are multiple header rows) prior to running the code. I've also given the user the flexibility to choose how many rows per page instead of looking to see how close to the bottom of the page, mostly because I couldn't get it to work. Any corrections/suggestions would be greatly appreciated.

[vba]Sub Splitlongtable()
Dim iLine As Integer
Dim stContTableTitle As String
Dim myDocument As Document
Dim TitleHeading As Style
iLine = InputBox("How many rows per page?", "Rows per page", 32)
Set myDocument = ActiveDocument
stContTableTitle = myDocument.Range(Start:=myDocument.Paragraphs(2).Range.Start, End:=myDocument.Paragraphs(2).Range.End)
Set TitleHeading = myDocument.Range(Start:=myDocument.Paragraphs(2).Range.Start, End:=myDocument.Paragraphs(2).Range.End).Style
Application.ScreenUpdating = False
'Get header rows
With Selection
.SelectRow
.Copy
End With
'split tables then insert header row and title
Selection.Tables(1).Select
Selection.Tables(1).AutoFitBehavior (wdAutoFitFixed)
While Selection.Rows.Count > iLine
With Selection
.Rows.First.Select
.MoveDown Count:=iLine
.splittable
.InsertBreak Type:=wdPageBreak
.InsertAfter (stContTableTitle)
.MoveDown Count:=1
.TypeParagraph
.PasteAppendTable
.Tables(1).Select
End With
Wend
'Apply correct style to table titles
Selection.WholeStory
With Selection.Find
.Replacement.Style = ActiveDocument.Styles(TitleHeading)
.Text = stContTableTitle
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
'delete extra title on first page
myDocument.Range(Start:=myDocument.Paragraphs(2).Range.Start, End:=myDocument.Paragraphs(2).Range.End).Select
Selection.Delete
Application.ScreenUpdating = True

End Sub
[/vba]

Happy New Year!

K