XLFind not working with many worksheets

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
drunkenmonkey
Pro Scripter
Posts: 79
Joined: Fri Jan 31, 2020 10:52 am

XLFind not working with many worksheets

Post by drunkenmonkey » Tue Aug 16, 2022 12:27 pm

Hi!
I have this XL file with many worksheets.
I want to find the a cell's value. Simple as that.
The xl function result is false and the returned value
from Val_PDBase var is Year.
Strange because the value to look for XLFind is located in All columns sheet, row 1 col 21.
Wonder what is the problem.

Here's the link to the file
https://u.pcloud.link/publink/show?code ... lfHHA7TdXV

Here's the sniplet code

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,0,xlBook
Let>val_1=Basic Coverage Guaranteed Life Insurance Amount
XLFind>%xlBook%,All Columns,%val_1%,isFound
XLGetSelectedCell>%xlBook%,Val_PDBase,Row_PDBase,Col_PDBase
XLQuit>xlBook
Thank you for your help! :D

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1106
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLFind not working with many worksheets

Post by Dorian (MJT support) » Tue Aug 16, 2022 1:05 pm

I've found that it helps if you select the correct sheet before using XLFind. You can do this with XLRunCode. Your script works as expected once we do that.

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,0,xlBook
Let>val_1=Basic Coverage Guaranteed Life Insurance Amount

//Select the correct sheet
XLRunCode>xlBook,Sheets("All Columns").Select

XLFind>%xlBook%,All Columns,%val_1%,isFound
XLGetSelectedCell>%xlBook%,Val_PDBase,Row_PDBase,Col_PDBase
XLQuit>xlBook
Important :

For XLRunCode to work 'Trust Access to the VBA project object model' must be enabled in Excel:

In Excel go to File/Options -> Trust Center -> Trust Center Settings -> Macro Settings.
Under 'Developer Macro Settings' enable 'Trust Access to the VBA project object model'
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 79
Joined: Fri Jan 31, 2020 10:52 am

Re: XLFind not working with many worksheets

Post by drunkenmonkey » Tue Aug 16, 2022 1:19 pm

Thank you Dorian, works good but the returned value for Col is 22
and it should be 21, can you tell me why.
Thank you so much for your help. :D

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1106
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLFind not working with many worksheets

Post by Dorian (MJT support) » Tue Aug 16, 2022 1:55 pm

That's very odd, as for me it's 21 (Col U) as expected. I'm scratching my head as to why this may be.

COL_PDBASE=21
ROW_PDBASE=1
VAL_PDBASE=Basic Coverage Guaranteed Life Insurance Amount

Although I have very occasionally seen Excel scripts not work as expected when Excel requires an update. So maybe look into that?

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2207 Build 16.0.15427.20166) 32-bit.
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 79
Joined: Fri Jan 31, 2020 10:52 am

Re: XLFind not working with many worksheets

Post by drunkenmonkey » Tue Aug 16, 2022 2:58 pm

Hi!

On my side Excel same version
https://u.pcloud.link/publink/show?code ... iN9SNiTGzV
thank you!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1106
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLFind not working with many worksheets

Post by Dorian (MJT support) » Tue Aug 16, 2022 3:17 pm

This may be a silly question but was the file I downloaded exactly the same as the one you're using? And are you using Macro Scheduler 15.0. 20?
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 79
Joined: Fri Jan 31, 2020 10:52 am

Re: XLFind not working with many worksheets

Post by drunkenmonkey » Tue Aug 16, 2022 3:22 pm

Hi!
Yes for both question. running on Win 10
https://u.pcloud.link/publink/show?code ... 0t3XuqtgpV
Thank you!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1106
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLFind not working with many worksheets

Post by Dorian (MJT support) » Tue Aug 16, 2022 3:48 pm

I'm completely unable to replicate this. Nothing I do forces it to misbehave.
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 79
Joined: Fri Jan 31, 2020 10:52 am

Re: XLFind not working with many worksheets

Post by drunkenmonkey » Tue Aug 16, 2022 4:04 pm

Hi Dorian,
I must have used another file because I downloaded the posted XL file and col no is 21.
My apologies for misguided you. :oops:
Have a great day and thank you for your precious help. :D

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1106
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLFind not working with many worksheets

Post by Dorian (MJT support) » Tue Aug 16, 2022 4:24 pm

Aaaah, that's good to know! :D
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts