PDA

View Full Version : Solved: Why is this code simply exiting without reason?



AlexMason
07-21-2010, 07:24 AM
Private 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 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
Dim bmkNO As String


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

For page = 1 To j

pageRNG.Copy
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.InsertParagraphAfter

With wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With

Next page


End Sub

j is a public variable by the way, as is page so dont worry that they arent defined up top.

anyway, the code gets to the line:

wrdDoc.Paragraphs(wrdDoc.Paragraphs.Count).Range.Paste

performs that paste and then simply stops....infact it stops and my user input form used to set all this off suddenly reappears infront of me like im just starting from the beginning. except i havent called it!

im actually stumped now, i have no idea whats going on.:banghead:

anyone got any ideas?

Simon Lloyd
07-21-2010, 08:52 AM
Well without the offending workbook i cant tell, your code works as it should do, have you checked that j is a number larger than 1? just enter Msgbox "the value of j is " & j right before the FOR loop, this will give you thye number for iterations that will take place.

gcomyn
07-21-2010, 12:51 PM
I've taken the code, and walked through it.... it does finish out the rest of the code.... I stepped through it, and it would continue as if I hit f5 after that line, so I put a break after it... and it hit.. and got through the rest of the code....

as far as I can see, it's working.

GComyn

mdmackillop
07-21-2010, 01:03 PM
I don't find any problems with your code (setting j = 3)

AlexMason
07-22-2010, 12:46 AM
right so theres something amiss else where in the code or the workbook.

ill see if this morning brings any change, since ive restarted my machine after having an uptime of nearly a month. shouldnt make a difference though.

how do i attach a work book to this post?

AlexMason
07-22-2010, 01:09 AM
Update: yep looks like it was just one of those things.... a fresh restart and the code works fine.

tho its pasting in the wrong order... i wanted it to paste, then break then paste the next one underneath but i think its pasting, putting a break in then pasting the next one above it.

no worries though, i can just alter which page it posts first.

Simon Lloyd
07-22-2010, 01:37 AM
For future how to add Attachments.

To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload.