Using Access 365 - Find value in Excel sheet using VBA

Question

I have code in an Access VBA module to try and find a value in a range from an excel workbook. This is the code that I have:

Set excelApp = CreateObject("Excel.application")

Set workbook = excelApp.Workbooks.Open(InputFileName)
Set Worksheet = workbook.Worksheets(1)

Dim rng As Range

rng = Worksheet.Range("A1:A10").Find("All Cards", LookIn:=xlValues)

The workbook opens, I can step through it, but on the last statement above, I get "91 - Object variable or With block variable not set"

I have also tried:

Set rng = Range("A1:A10").Find(What:="Holdings All Cards", LookIn:=xlComments).Select
strOrig = ActiveCell.Value

And I get the same error message. What am I doing wrong? I have all the proper references set for the Microsoft Excel Object Library and Microsoft Scripting Runtime.

Answer 1

I did some more research. The code samples above, I took from Visual Studio and copied it into Access. I didn't realize at the time that the VBA code that I tried to run would be different. Well, it is. LOL! Anyway, I reworked the code:

From Visual Basic:

Dim rng As Range
rng = Worksheet.Range("A1:A10").Find("All Cards", LookIn:=xlValues)

Set rng = Range("A1:A10").Find(What:="Holdings All Cards", LookIn:=xlComments).Select
strOrig = ActiveCell.Value (I didn't use this as the ending .Select was in error)

Modified in Access:

Set rng = Range("A1:A8").Find("All Cards", LookAt:=xlPart, MatchCase:=False)

I am NOT going to use any VBA code samples from my VB Project, but rewrite them using VBA for Access/Excel. Hope this helps anyone else.