Pulling cell values from Excel into Word (Runtime error 429)

2
votes

I've been having trouble taking cell values from excel and using them in a word macro (I'm trying to insert string values from sheet cells at various bookmarks in a word doc). Right now I'm just trying to be able to access cell values, but I'm coming up with an error 429 (ActiveX component can't create object). Any help/advice on how to approach pulling values from excel and using them in word would be appreciated.

Dim objExcel As New Excel.Application
Dim exWbs As Excel.Workbooks
Dim exWb As Excel.Workbook
Dim strWbName As String
Dim cisInfo As CIS

Sub PopulateDoc()
    Set exWb = New Excel.Workbook
    exWb = exWbs.Open("CIS.xlsx")
    exWb.Sheets("Property Information").Cells(2, 8).Value = "test"
End Sub
1
vbaexcelms-word

1 Answers

0
votes

You cannot create a New instance of a WorkBook instead:

Set exWb = objExcel.Workbooks.Open("CIS.xlsx")

(You also need to have added a reference to the Microsoft Excel XX Object Library)