Word VBA - addressing the last paragraph (to apply a style)

I'm loving this. But stuck at the next step.

I have grabbed the 8,500+ paragraphs in a 411 page document (with Crystal's code from here) and stuck them in an Access table via ADO and SQL. It was fairly quick, incidentally. Works great.

I now use this to get the data from that table and loop through the recordset and dump them on a blank Word document. That works great too. (My expertise is in Excel/ADO/SQL. I was surprised to be able to adapt it to Word VBA quite easily. (I'm a Word VBA baby).

Here's my next step. I hope the experts will nudge me in the right direction.

Another field in the recordset retrieved is "Style", which is right there and available on each iteration of the loop (Whoopie!). That is rsData.Fields.Item("Style").Value
It should be easy (but not for me) to apply that style to the text that the loop has just pasted.
BUT, I see that the range rngNew is always from the 'top the document to the current end point'. This is not the range I want. I want the text paragraph that's just been inserted. (hmmm .. that would be the last paragraph on each iteration)

Thanks for just a hint. I've learned so much in a week. (The Word object model is sooooo different to Excel's. Naturally)

Running this in Word 2016. Takes about 10 seconds for 8,500+ paragraphs on about 400 pages.
Sub GetData_andPaste()

    Dim rsData As ADODB.Recordset
    Dim sSQL As String
    
    Dim docActive As Document
    Dim rngNew As Range
    
    Dim dteStart As Date
    Dim dteEnd As Date
    
    Application.ScreenUpdating = False
    
    Set docActive = ThisDocument
    Set rngNew = docActive.Range
    
    dteStart = Now
    
    Call DBConnectionAccess
    
        sSQL = "SELECT * FROM tblParagraphs WHERE SectionNumber >= 5"

        Set rsData = New ADODB.Recordset
        rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

        rsData.MoveFirst
        If Not rsData.EOF Then
        
            Do Until rsData.EOF
            
                If rsData.Fields.Item("SectionNumber").Value >= 5 Then
                    rngNew.InsertAfter Text:=rsData.Fields.Item("ParagraphText").Value & vbCr
                    'rngNew.Select      'commented out. This is for examamining what rngNew is
                End If
                
                rsData.MoveNext
                
            Loop
            
        End If
        
    Call DBConnectionClose
    
    Application.ScreenUpdating = True
    
'to see how long it took
    dteEnd = Now    
    rngNew.InsertAfter Text:=dteStart & vbCr    
    rngNew.InsertAfter Text:=dteEnd
    
    MsgBox "Done"

End Sub

Open in new window


Crystal's code is here and here.
hindersalivaAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
So for each record you are inserting a paragraph with the text from the field ParagraphText  and you want to apply the style that's in the corresponding Style field to the inserted text/paragraph?

If it's always going to be the last paragraph then something like this might work.
With ThisDocument
    .Paragraphs(.Paragraphs.Count).Range.Style = rsData.Fields("Style").Value
End With

Open in new window


[/code]
0
 
hindersalivaAuthor Commented:
Norie, the range that I end up is beyond the paragraph I'm looking to apply the Style to. So I did Count-1 and got somewhere with it.

But, here's what I found that may means I'l have to abandon this particular avenue. The reason is, while the code to get and paste TEXT from the Access DB with ADO/SQL is very fast (10 sec) when I add the line to apply the relevant Style to it it seems to (at least looks like)  slow down a lot. Or it crashes.

(Anyone following what I'm trying to do ... to hold paragraphs and style names in an Access table, so that the users can assemble the document on demand. The sample I'm working with has 8,500+ paragraphs))

Oh well. I shall be back with Plan B, or is it C?

Meanwhile, I'd be grateful for any encouragement to persevere with the 'add text and add style' idea. If adding the styles takes the same amount of time as adding the text (10 sec) I'll be happy. Shouldn't it?
0
 
NorieVBA ExpertCommented:
How about using a field that already has the formatting you want?

Export rtf field to MS word
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
GrahamSkanConnect With a Mentor RetiredCommented:
You can get the last paragraph object in a document using the .Last method:
docActive.Paragraphs.Last.Style = "Heading 2"

Open in new window

Depending on the circumstance, the last paragraph might remain empty, so the last one with text will be the one before that.
docActive.Paragraphs.Last.Previous.Style = "Heading 2"

Open in new window

1
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
hi,

adding to Graham's comment, you can also apply a style to a particular paragraph number.  I didn't know about .Last and .Previous, thank you Graham! I have been doing this to apply a style to the last paragraph (which is actually next to last since there is a paragraph marker after):
oDoc.Paragraphs(oDoc.Paragraphs.Count - 1).Style = oDoc.Styles("MyStyleName")

Open in new window

WHERE
oDoc is the Word document object (hence "o" in the beginning of the variable name)

I used  oDoc.Styles("MyStyleName") instead of  simply "MyStyleName"  because my code defines styles in the document that may be different, and I want the reference to explicitely use what I defined.

have an awesome day,
crystal
0
 
hindersalivaAuthor Commented:
Sorry. I allocated the points before I saw Norie's suggestion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.