Excel VBA output to word with formatting

[diedrich08]

Hello community,

I have been attempting to export an output of an excel macro to a word document. I am able to successfully export the information I require, however am having difficulty formatting the output. I am able to format the text with a "with/end with" statement for common changes such as bold or underline, but unable to format the text as a heading. Can anyone out there direct me on how to have my output of the

Code: Select all

wdDoc.Paragraphs(ParaCount).Range = Table_2W.Range("$B" & a)

be a MS word heading (code is below).

Thanks for the help, it's greatly appreciated!

Diedrich

Code: Select all

    'Export Output to MS Word
        'Initialize the Excel objects
        Set wbBook = ThisWorkbook
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Open(wbBook.Path & "\" & stWordDocument)

        
        'Add paragraph to word doc
        wdDoc.Paragraphs.Add
        
        'Count paragraphs
        ParaCount = wdDoc.Paragraphs.Count
        
        'Place heading in final paragraph
        wdDoc.Paragraphs(ParaCount).Range = Table_2W.Range("$B" & a)
        
                              
            'Save and close the Word doc.
            With wdDoc
            .Save
            .Close
            End With
            
            wdApp.Quit
            
            'Null out the variables
            Set wdDoc = Nothing
            Set wdApp = Nothing

[HansV]

Try this:

Code: Select all

        wdDoc.Paragraphs(ParaCount).Style = wDoc.Styles(wdStyleHeading1)

[diedrich08]

As always Hans, the solution works great.

Thanks again for all your work on Eileen's Lounge!

Diedrich

PS - for those of you who will use this solution, I used the following code:

Code: Select all

wdDoc.Paragraphs(ParaCount).Style = wdDoc.Styles(wdStyleHeading1)

[diedrich08]

Hello,
I have been running into an error in exporting to word. I am running the following code on a loop:

Code: Select all

'Define Word objects
Dim wdApp As Object
Dim wdDoc As Object
                        
    'Set wdApp and wdDoc and make word doc visible
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open("C:\Users\Kevin Long\Documents\Auto_Reporting_Tool\Word_Output\AutoReport_" _
        & Data.Cells(2, 2) & "_" & Format(Date, "yyyy.mm.dd") & ".docx")
    wdApp.Visible = True

I am copying multiple items to the same document as specified within the loop. I think I am getting the error because excel keeps trying to open my where I want everything saved. Is there a way I can alter this code so that as it runs through the loop, it doesn't crash/run forever?

Thanks

Kevin

[HansV]

I'd place the code that you mention before (above) the loop, so that Word gets started only once, and the document is opened only once.

[diedrich08]

Thanks Hans,

Works great and I knew it would be an easy fix. I think I had been staring at my code for too long today as I should have figured this one myself.

Lesson learned!

Thanks again for your work on Eileen's Lounge

Diedrich