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
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,
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.
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.
Answered By – Timothy Rylatt
Answer Checked By – David Goodson (AngularFixing Volunteer)