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.
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.