question

GaryAgoura-8910 avatar image
0 Votes"
GaryAgoura-8910 asked GaryAgoura-8910 edited

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.

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GaryAgoura-8910 avatar image
0 Votes"
GaryAgoura-8910 answered GaryAgoura-8910 edited

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.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.