Insert Break ???

K

Kim

I believe I initially posted this in the wrong group. Becuase of that I am
re-posting here.
Please pardon the dupe.



I am very new at working with VBA and programming word documents.

What I am doing is connecting to a database and looping through a recordset.

the recordset contains one field. I want to build a single word document
with each field on its own page.
I have it all working with one exception - the page break.


I cannot grasp what I am doing wrong. It seems that it should be easy - but
I am missing the bigger picture - somewhere -
here is what I have.


Can someone take pity on me and show me the error of my ways?

Thanks in advance.

oDoc is the Word.Document object
eRS is the recordset I am looping through
myRange is a Word.Range object
------------------------------------------------------

If Not eRS.EOF Then
Do While Not eRS.EOF
With oDoc
.Content.Font.Name = "courier"
.Content.InsertAfter Text:=eRS("text").Value
End With

Set myRange = ActiveDocument.Range
With myRange
.Collapse wdCollapseEnd
.End = ActiveDocument.Range.End
.Collapse wdCollapseEnd
.InsertBreak wdPageBreak
End With

With oDoc
.SaveAs FileName:=strFolder & strFilePre & ".Doc"
End With

eRS.MoveNext
Loop

End If

------------------------------------------------------
 
D

Dave Lett

Hi Kim,

The following should work:

If Not eRS.EOF Then
With oDoc
Do While Not eRS.EOF
.Content.InsertAfter Text:=eRS("text").Value
Set myRange = ActiveDocument.Range
With myRange
.Collapse wdCollapseEnd
.InsertBreak wdPageBreak
End With
eRS.MoveNext
Loop
.Content.Font.Name = "courier"
.SaveAs FileName:=strFolder & strFilePre & ".Doc"
End With
End If


HTH,
Dave
 
K

Kim

Hi Dave,
thank you for your help - but I still end up with no page breaks.
Any other ideas?
this seems like it should be so easy -

Thanks,
Kim
 
K

Kim

I am getting a continuos page break.

one record from the record set after the other -
where there should be a pagebreak between each record
 
D

Dave Lett

Kim,

Continuous page break? There is no such thing. Do you mean continuous
_section_ break?

Dave
 
K

Kim

yes that must be.
they break where they have to - it is not breaking where I want it to...
thanks so much for the help
 
D

Dave Lett

Try sending me a sample of your finished document (5 pages should be plenty)
so that I can see what breaks we're ending up with.
You can send it to dlett_at_smconst_dot_com

replace _at_ with "@" and _dot_ with "."

Dave
 
K

Kim

Dave,
thanks for helping - but I figured out why I was getting the wrong
results...
I was getting the results from an incorrect stored procedure.

Now I have it working correctly.
Thank you for all of your help...

Here's the code


Sub dave()

Dim ADOCon As New ADODB.Connection
Dim oDoc As New Word.Document

Dim strFolder As Variant
Dim strFile As Variant
Dim cDir As String
Dim strFilePre

cDir = "D:\WORK_TASKS\LCRC_LEADS\"
strFolder = "D:\WORK_TASKS\LCRC_LEADS\"

strFilePre = Replace(Date, "/", "_") & "_KIM"

ADOCon.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=XXXX; " & _
"Initial Catalog=XXX; " & _
"User ID=XXX; " & _
"Password=XXX; " & _
"network library=dbmssocn ; "

On Error Resume Next
ADOCon.Open

If Err.Number <> 0 Then
MsgBox Err.Description
On Error GoTo 0
Exit Sub
End If

On Error GoTo 0

'Open a recordset
Dim ADORS As New ADODB.Recordset

With ADORS
.ActiveConnection = ADOCon
.Open "EXEC getCompleteLeadDoc"
End With

Dim myrange As Range


If Not ADORS.EOF Then
With oDoc
Do While Not ADORS.EOF
.Content.InsertAfter Text:=ADORS("body").Value
Set myrange = ActiveDocument.Range
With myrange
.Collapse wdCollapseEnd
.InsertBreak wdPageBreak
End With
ADORS.MoveNext
Loop
.Content.Font.Name = "courier"
.SaveAs FileName:=strFolder & strFilePre & ".Doc"
End With
End If



With oDoc
..SaveAs FileName:=strFolder & strFilePre & ".Doc"
End With

ADORS.Close

ADOCon.Close

Set ADOCon = Nothing
Set ADORS = Nothing


End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top