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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
NorieAnalyst Assistant Commented:
How about using a field that already has the formatting you want?

Export rtf field to MS word
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

GrahamSkanRetiredCommented:
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, AccessRemote 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.