Apply heading style in VBA from Excel to Word

Question

Here is the code i have in Excel to control a word document, and publish it with some data. I would like to create some of the text in different styles, but keep getting Run time error 430 (Class does not support Automation or does not support expected interface)

Here is the code:

'Create the word document
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add
    objWord.Visible = True
    Set objSelection = objWord.Selection


    For i = 2 To 94
        'Heading 1
        If myRange(i - 1, 1) <> myRange(i, 1) Then
            objSelection.TypeParagraph
            objSelection.Style = ActiveDocument.Styles("Heading 2")
            objSelection.TypeText Text:=myRange(i, 1)
        End If
        objSelection.TypeParagraph
        objSelection.Style = ActiveDocument.Styles("Heading 3")
        objSelection.TypeText Text:=myRange(i, 2)
        For k = 3 To 12
            objSelection.TypeParagraph
            objSelection.Style = ActiveDocument.Styles("Heading 4")
            objSelection.TypeText Text:=myRange(1, k)
            objSelection.TypeParagraph
            objSelection.Style = ActiveDocument.Styles("Normal")
            objSelection.TypeText Text:=myRange(i, k)
        Next
    Next

Some initial thoughts: your myRange variable, is that a Excel.Range object? if its is, you are not providing correct value for the object. Looks like you are using Range as a Cell. Also, there is a Word library you can reference in Excel VBA. Finally, as Dirk said, please specify where the error occurs — Zac
i used the library of Word. the only problem is with the objSelection.Style = ActiveDocument.Styles("___"). which is not working. The rest is working just fine. — user3016795

user3598756 user3598756 · Accepted Answer · 2016-09-15T07:50:57

you have to:

set Selection object of the wanted document any window
```
Set objSelection = objDoc.ActiveWindow.Selection
```

explicitly reference Word application active document:

objSelection.Style = objWord.ActiveDocument.Styles("Heading 2")

you may also want to use With - End With syntax to clean up your code and make it more readable, robust and faster

Option Explicit

Sub main()    
'' "early binding" case
'' requires adding Microsoft Word XX.Y Object Library" reference to your project
'''    Dim objWord As Word.Application '<--| "early binding" requires referencing 'Word' application explicitly
'''    Dim objDoc As Word.document '<--| "early binding" requires referencing 'Word' application explicitly
'''    Dim objSelection As Word.Selection '<--| "early binding" requires referencing 'Word' application explicitly

' "late binding" case
    Dim objWord As Object
    Dim objDoc As Object
    Dim objSelection As Object

    Dim myRange As Range '<--| for Excel objects, referencing 'Excel' explicitly is optional

    Dim i As Long, k As Long '<--| VBA variables

    Set myRange = ActiveSheet.Range("myRange") '<-- set myRange range object to your active worksheet named range "myRange"

    Set objWord = CreateObject("Word.Application") '<--| get a new instance of Word
    Set objDoc = objWord.Documents.Add '<--| add a new Word document
    objWord.Visible = True
    Set objSelection = objDoc.ActiveWindow.Selection '<--| get new Word document 'Selection' object

    With objSelection '<--| reference 'Selection' object

        For i = 2 To 94
            'Heading 1
            If myRange(i - 1, 1) <> myRange(i, 1) Then
                .TypeParagraph
                .Style = objWord.ActiveDocument.Styles("Heading 2")
                .TypeText Text:=myRange(i, 1).Text
            End If
            .TypeParagraph
            .Style = objWord.ActiveDocument.Styles("Heading 3")
            .TypeText Text:=myRange(i, 2).Text
            For k = 3 To 12
                .TypeParagraph
                .Style = objWord.ActiveDocument.Styles("Heading 4")
                .TypeText Text:=myRange(1, k).Text
                .TypeParagraph
                .Style = objWord.ActiveDocument.Styles("Normal")
                .TypeText Text:=myRange(i, k).Text
            Next
        Next
    End With

    objDoc.SaveAs "C:\Users\...\Desktop\Doc1.docx" '<--| save your word document

    objWord.Quit '<--| quit Word
    Set objWord = Nothing '<--| release object variable        
End Sub

Apply heading style in VBA from Excel to Word

1 Answers