Not able to activate worksheets-CodePudding

So normally when using the VBA editor, code autosuggests from available methods.properties for whatever object your referencing. I'm trying to pull data from an excel sheet into a word document using a macro on the word doc, but whenever I try to use worksheets.activate, no autosuggestion for activate comes up, leading me to think it's not actually activating. Neither can I use it from a VBA script in excel.

My script is still in it's beginning stages, but here it is so far:

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 doc, and not a sheet. So 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.

Any pointers are appreciated, thanks.

CodePudding user response：

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

CodePudding user response：

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.