PDA

View Full Version : Excel to Word... little help required



AlexMason
07-20-2010, 07:26 AM
Right so my 3rd sticking point of the week! and its only tuesday! i wish they'd hire proper programmers here and not make the engineers do it.

anyway

im trying to copy a formatted and completed table from Excel, into word.

now i can open word, create a new document, set the page up with the right margins and paste the table in. however, i plan to have a For loop based on the number of tables to paste.

so what i need to do is once a table is pasted, i need to put in a section break to next page or a page break or something to start the cursor on a new page and then paste the 2nd table in. then again, a break of some sort so i can paste the 3rd table into page 3 and so on.

heres what i coded. It wont execute the InsertBreak command... im guessing because im trying to break the table and not the page. i dunno.

Public Sub wordGEN()

'in here, need code that opens a word document, new, and then generates a table? or opens a template table (might be easier) and then uses
'uses the public variables to operate on excel table, brining the data over to the word table.

Dim wrdApp As New Word.Application
Dim wrdDoc As Word.Document
Dim pageRNG As Range
Dim HP As Worksheet
Dim IP As Worksheet


Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.ADD

With wrdDoc

.PageSetup.RightMargin = CentimetersToPoints(1.27)
.PageSetup.TopMargin = CentimetersToPoints(1.27)
.PageSetup.BottomMargin = CentimetersToPoints(1.27)
.PageSetup.LeftMargin = CentimetersToPoints(1.27)

End With

Set pageRNG = Sheets("HP").Range("B2:H35")

pageRNG.Copy

With wrdDoc

.Content.Paste
.InsertBreak Type:=wdPageBreak

End With


End Sub
what am i doing wrong?

(ignore the set PageRNG bit, what i plan to do is have the range set to that but then offset it by x each time through the loop when i get to that point. at the moment im just trying to get it to move onto a new page once its pasted a table)

mdmackillop
07-21-2010, 12:05 AM
Hi Alex
Why not create a template containing section breaks and bookmarks within each. You can the paste your tables to successive bookmarks.

AlexMason
07-21-2010, 12:46 AM
Hmmm i might give it a try, but its all new to me... lll have to search around to see how to do it

the main problem though is it wont be just me using this macro.... the template files gonna have to follow this thing around. it would be much simpler for it to just create a new document rather than open a template. ill keep it as an option though if i cant get that to work

AlexMason
07-21-2010, 01:42 AM
could i actually put bookmarks in as part of the code before pasting the tables....?

so based on the number of tables i have i create that number of book marks and then cycle back to the top and begin pasting?

AlexMason
07-21-2010, 02:05 AM
grrr nothing works!

i cant seem to use anything like

Selection.EndKey Unit:=wdLine
Selection.TypeParagraph
selection.InsertBreak Type:=wdSectionBreakNextPage

and it wont add bookmarks either... and i dont get it because i just recorded a macro where i add a bookmark then a section break and it works just fine there! how come it wont work when im doing it in code executed from excel?

i keep getting runtime error '438'

Object doesnt support this property or method