Using Clipboard to compare to data in excel sheet

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Using Clipboard to compare to data in excel sheet

Post by joansdan » 27 Jun 2022, 04:49

Hi,

I would like to know how to use Clipboard to compare to data in Excel spreadsheet.

Thank you

User avatar
mikeyww
Posts: 26599
Joined: 09 Sep 2014, 18:38

Re: Using Clipboard to compare to data in excel sheet

Post by mikeyww » 27 Jun 2022, 05:43

Code: Select all

Clipboard = abc
Sleep, 100
XL := ComObjActive("Excel.Application")
For c In XL.Selection
 If (Clipboard = c.Text)
  MsgBox, 64, Found it!, % c.Address
You can also search this forum for "Excel" and find many additional examples. Enjoy!

joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Re: Using Clipboard to compare to data in excel sheet

Post by joansdan » 27 Jun 2022, 07:10

Hi,

And thank you for reply.

Can I put say " Bypass" using Set Clipboard. and have Clipboard search for that in spreadsheet? Or put in Clipboard "Bypass' and have a GOTO Routine?

What i am trying to do is if certain conditions exist in spreadsheet, bypass it, or process it.

I really do appreciate you expertise.

Thank you

User avatar
mikeyww
Posts: 26599
Joined: 09 Sep 2014, 18:38

Re: Using Clipboard to compare to data in excel sheet

Post by mikeyww » 27 Jun 2022, 07:32

Code: Select all

Clipboard = Bypass
Sleep, 100
; ----------------------------
If xlFindAll(Clipboard)
     MsgBox, 64, Success, Found!
Else MsgBox, 48, Failure, Not found.
 
xlFindAll(needle, rng := "", xl := "") { ; https://www.autohotkey.com/boards/viewtopic.php?p=156838#p156838
 ; https://www.autohotkey.com/boards/viewtopic.php?p=462637#p462637
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 While (address != (found := rng.FindNext(found)).Address)
  result := xl.Union(result, found)
 Return result
}

joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Re: Using Clipboard to compare to data in excel sheet

Post by joansdan » 27 Jun 2022, 09:14

Hi,

You are very knowledgeable, and thank you. One last question.

Is there a simple way to move a "Process" or "Bypass" statement to a GOTO command from Excel

I appreciate your help, and your knowledge.

Thanks

User avatar
mikeyww
Posts: 26599
Joined: 09 Sep 2014, 18:38

Re: Using Clipboard to compare to data in excel sheet

Post by mikeyww » 27 Jun 2022, 09:27

You can use Goto, though :arrow: Gosub is usually a better choice (if not a function).

Explained: Labels

Code: Select all

F3::
If xlFindAll(Clipboard)
     Gosub, Found
Else Gosub, NotFound
Return

Found:
MsgBox, 64, Success, Found!
Return

NotFound:
MsgBox, 48, Failure, Not found.
Return
 
xlFindAll(needle, rng := "", xl := "") { ; https://www.autohotkey.com/boards/viewtopic.php?p=156838#p156838
 ; https://www.autohotkey.com/boards/viewtopic.php?p=462637#p462637
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 Try found := rng.Find(needle, rng.Cells(rng.Cells.Count))
 If !found
  Return
 result := found, address := found.Address
 While (address != (found := rng.FindNext(found)).Address)
  result := xl.Union(result, found)
 Return result
}

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Using Clipboard to compare to data in excel sheet

Post by flyingDman » 27 Jun 2022, 11:30

While XlFindAll() is a very clever function, it is particularly good at returning an array of the found cells. If the purpose here is just to find out if a certain string exists in the spreadsheet (and if so do something), the following is all you need:

Code: Select all

Clipboard = x
gosub, % ComObjActive("excel.application").activesheet.usedrange.Find(Clipboard,,,2) ? "go" : "nogo"
return

go:
msgbox Found
return

nogo:
msgbox Not Found
return
Note that the 4th parameter of Find() is 1: "Match against the whole of the search text" or 2: "Match against any part of the search text".
14.3 & 1.3.7

joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Re: Using Clipboard to compare to data in excel sheet

Post by joansdan » 27 Jun 2022, 14:35

Thank you.

joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Re: Using Clipboard to compare to data in excel sheet

Post by joansdan » 27 Jun 2022, 15:54

Hi,

I am very new at this, and wonder how do I enter this code into Macro?

I do appreciate the help.
thank you

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Using Clipboard to compare to data in excel sheet

Post by flyingDman » 27 Jun 2022, 16:08

It might make sense to have a look here: https://www.autohotkey.com/docs/AutoHotkey.htm (as a starting point).

Note: Autohotkey is a programming language. It is probably much more accessible than higher end languages, but it will still be a steep learning curve for most. If you intend to wing it, without documentation, you will not get very far. Relying only on the forum, is also probably not a good idea.
14.3 & 1.3.7

joansdan
Posts: 6
Joined: 27 Jun 2022, 04:42

Re: Using Clipboard to compare to data in excel sheet

Post by joansdan » 28 Jun 2022, 07:30

Hi,

And thank you for Link. I am just trying to get extra credit project done, before end of semester. Will try to get it done using the link.

Thanks again

User avatar
mikeyww
Posts: 26599
Joined: 09 Sep 2014, 18:38

Re: Using Clipboard to compare to data in excel sheet

Post by mikeyww » 28 Jun 2022, 07:40

Please have Prof mark "A" in our records. Thanks.

Post Reply

Return to “Ask for Help (v1)”