How to get autosuggestions for Excel worksheets in Word VBA editor?

Question

Normally when using the VBA editor, code autosuggests from available methods.properties for whatever object being referenced.

I'm trying to pull data from an Excel sheet into a Word document using a macro on the Word document. Whenever I try to use worksheets.activate, no autosuggestion for activate comes up, leading me to think it's not activating. Neither can I use it from a VBA script in Excel.

My script is still in it's beginning stages:

Sub Populate()
 Dim doc As Document
 Set doc = ActiveDocument
 
 Dim appXL As excel.Application
 Set appXL = CreateObject("excel.Application")
 
 Dim partnerNames As excel.Workbook
 Dim ihmNames As excel.Workbook
 Set partnerNames = appXL.Workbooks.Open("D:/Database/Imports and Exports/Funder Credit Lists/2022-01 Partners.csv")
 Set ihmNames = appXL.Workbooks.Open("D:\Database\Imports and Exports\Funder Credit Lists\2022-01 IHM.csv")
 
 appXL.Worksheets(Left(partnerNames.Name, Len(partnerNames.Name) - 4)).Activate
 
 Dim lastRow As Long
 lastRow = appXL.Cells.Find(What:="*", After:=Range("C1"), SearchOrder:=xlByRows, searchDirection:=xlPrevios).Row
 appXL.Range("A1").Resize(lastRow, 3).Select
 
 'Insert Hero Names
 Dim hero As Range
 Set hero = doc.Range(Start:=doc.Bookmarks("Hero").Start, End:=doc.Bookmarks("Hero").End)
 hero.InsertAfter ("IT WORKS!!!")
 
End Sub

The lastRow = appXL.Cells..... is causing a type mismatch, which I believe is being caused by the fact that appXL.Cells refers to the active sheet, and the ActiveDocument is a Word document.
That leads me to activating the sheet, but trying to do so causes the error "Subscript out of range," even if I explicitly type the sheet name.

Answer 1

So the problem was specifically the "After:=Range" portion in the appXL.Cells.Find function. I forgot that, since I'm working from a word doc and not an excel, I needed to specify appXL.Range instead of just Range. Oh the joy of finding out my weeklong problem was just a simple missed class specification.

That said, thanks to @Eugene for informing me of the Object Browser window. That was useful.

Answer 2

appXL is an excel.Application object. Worksheets property belongs to Workbook class. You can use just Worksheets.(...) to refer the active workbook sheets. Same to property .Cells

Or you can define a new Workbook variable and handle it:

Dim wbXL as Workbook
set wbXL = ActiveWorkbook
wbXL.Worksheets(...).Activate

Answer 3

It seems you just need to add an Excel COM reference in Word VBA to be able to get auto-suggestions. From the Tools menu, choose References to display the References dialog box. The References dialog box shows all object libraries registered with the operating system. Scroll through the list for the application whose object library you want to reference. References whose check boxes are selected are used by your project; those that aren't selected are not used, but can be added. Select the object library reference in the Available References box in the References dialog box and choose OK. Your Visual Basic project now has a reference to the application's object library. If you open the Object Browser (press F2) and select the application's library, it displays the objects provided by the selected object library, as well as each object's methods and properties. In the Object Browser, you can select a class in the Classes box and select a method or property in the Members box.