-
Gary Agoura 201 Reputation points
2021-11-02T19:43:46.58+00:00 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.
Using Access 365 - Find value in Excel sheet using VBA
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.