Word table import into Excel type mismatch-CodePudding

I have the macro below to add a column to Word tables and paste a value in each cell to last row. It runs perfectly in Word, but when run inside an Excel macro it adds the new columns correctly, but then gives a type mismatch error for the ranges (tblA, tblB, tblC). Can someone please help identify the error? Many thanks!

Sub AddTeamColumn()

Dim WordApp As Object, WordDoc As Object
Dim arrFileList As Variant, Filename As Variant
Dim tblA As Range, tblB As Range, tblC As Range

'On Error Resume Next

arrFileList = Application.GetOpenFilename("Word files (*.doc; *.docx),*.doc;*.docx", 2, _
                                          "Select files", , True)

If Not IsArray(arrFileList) Then Exit Sub

Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True

For Each Filename In arrFileList

    Set WordDoc = WordApp.Documents.Open(Filename, ReadOnly:=False)
    
            WordDoc.Tables(3).Columns.Add
            WordDoc.Tables(4).Columns.Add
            WordDoc.Tables(5).Columns.Add
            
            Set tblA = WordDoc.Range(Start:=WordDoc.Tables(3).Cell(2, 8).Range.Start, _
                     End:=WordDoc.Tables(3).Rows.Last.Range.End)

            Set tblB = WordDoc.Range(Start:=WordDoc.Tables(4).Cell(2, 9).Range.Start, _
                     End:=WordDoc.Tables(4).Rows.Last.Range.End)

            Set tblC = WordDoc.Range(Start:=WordDoc.Tables(5).Cell(2, 5).Range.Start, _
                     End:=WordDoc.Tables(5).Rows.Last.Range.End)
                            
            WordDoc.Tables(1).Cell(1, 1).Select
            Selection.Copy
            'tblA.PasteSpecial DataType:=wdPasteText 'Word
            tblA.PasteSpecial xlPasteValues 'Excel
            
            WordDoc.Tables(1).Cell(1, 1).Select
            Selection.Copy
            'tblB.PasteSpecial DataType:=wdPasteText 'Word
            tblA.PasteSpecial xlPasteValues 'Excel
            
            WordDoc.Tables(1).Cell(1, 1).Select
            Selection.Copy
            'tblC.PasteSpecial DataType:=wdPasteText 'Word
            tblC.PasteSpecial xlPasteValues 'Excel

        WordDoc.Save

Next Filename

WordApp.Quit

Set WordDoc = Nothing
Set WordApp = Nothing

End Sub

CodePudding user response：

When running from Excel you have to define the tbl-Ranges explicitly as Word.Range - otherwise they expect Excel-Ranges (therefore the type mismatch) - or use object if you want to keep with late binding.

If you can go with early binding then you have to add Microsoft Word as Reference to your VBA project.

'early binding
Dim tblA As Word.Range, tblB As Word.Range, tblC As Word.Range

'late binding
Dim tblA As object, tblB As object, tblC As object

Same thing with Selection: you have to use wordapp.Selection otherwise Excel selection is referenced.