I'm working on a VBA Word Macro to create a document with personalized headers. I have a spreadsheet with the text to be inserted in the Headers (every row shall be inserted on an individual page).

To do so, I want to import the data from spreadsheet as an Array into Word, in order to get those texts in place.

I wrote a piece of code to open the spreadsheet and get a pre-defined range using Select method; I get an error on trying to pass this Selection to a Range object.

The error message is Runtime Error 13: Incompatible types.

I added an If loop to assure that the Selection is not empty, and it wasn't, for every time I ran the code.

Sub main()

    'Main procedure
    Dim app_Excel As Excel.Application
    Set app_Excel = CreateObject("Excel.Application")
        
    Dim wbk_srce As Workbook
    Set wbk_srce = app_Excel.Workbooks.Open("C:\0_portolon\Dias.xlsm", , False)
    
    Dim wsh_srce As Worksheet
    Set wsh_srce = wbk_srce.Worksheets(3)
    wsh_srce.Activate
    
    cell_1 = CStr("A1")
    cell_2 = CStr("D216")
    
    Dim header_range As Range
    wsh_srce.Range(cell_1, cell_2).Select
    
    If Selection = Empty Then
        Debug.Print ("error")
    Else
        Debug.Print ("good")
    End If
    
    Set header_range = Selection    '<---Error
    
    Dim header_array() As Variant
    
    'header_array = header_range.Value
    
    'Call add_readers


End Sub

Note: I added only the Microsoft Excel 16.0 Object Library to VBA Word.

How can I make this work?

Thanks in advance,

Tiago

CodePudding user response：

Both Word and Excel have a Selection object so when you refer to a selection in Excel you need to qualify it by prefixing it with your Excel Application variable, i.e. app_Excel.Selection.

You also need to do this with other objects that both applications have in common such as Range. Word will understand Dim header_range As Range as being a Word.Range, so assigning a selection in Excel to it isn't going to work.