AutoHotkey Community

Posted: **17 Mar 2022, 14:46**

Hello everyone,

I'm trying to write a script that should read a value from the clipboard, look for this value in an Excel spreadsheet,

and then if the value is found, the entire line where the value was found should be copied to the clipboard.

Here what I've found/written so far:

Code: Select all

#NoEnv
#Persistent
#SingleInstance,Force
#x::
{
Clipboard:="" ; clear clipboard
Send, ^c ; Marked text is copied to clipboard
PXL := ComObjGet("C:\MyFolder\test.xlsx")
;Find_And_Return_Position
XL_Find_And_Return_Position(PXL,RG:="",Search:="%clipboard%",Absolute:=0,Instance:=1){
  RG:=(RG)?(RG):(XL_Used_RG(PXL,0)) ;If Range not provided, default to used range
 Index:=0
 For Cell in PXL.Application.ActiveSheet.Range(RG) { ;Use For loop to iterate over each cell in range
 if Cell.Value=(Search) { ;Stop looping if you find the value
 Index++ ;Increment Index
 If (Index=Instance){ ;If this is the correct instance
 if Absolute
 Return Cell.address ;;~  Cell with $ in them
 Else Return Cell.address(0,0) ;;Cell without $ in them
 /*
 Return Absolute?Cell.Address:Cell.Address(0,0)
 */
 }}
 } Return "Not found" ;If finish looping then it was not found
}
;Copy_to_Clipboard
XL_Copy_to_Clipboard(PXL,RG:="%Cell.address%"){
 PXL.Application.ActiveSheet.Range(Cell.address).Copy ;copy to clipboard
}
}

[Mod edit: [code][/code] tags added.]

who can help, many thanks in advance

Posted: **17 Mar 2022, 16:40**

Will there possibly be more than one row found? Try this to return the content of row containing the first found cell:

Code: Select all

Clipboard := ""	
Send, ^c
Clipwait, 1
needle := clipboard 									
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"

xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
rw := xl.Cells.Find(needle,,,1).row
xl.rows(rw).entirerow.cells.copy
wrkbk.close(0)
xl.quit()
msgbox % clipboard
return

forget about that function.

Also please use the <code></code> tags and use the proper indentation (that makes reading it so much easier). Thks.

Posted: **18 Mar 2022, 04:03**

flyingDman wrote: ↑
17 Mar 2022, 16:40
Will there possibly be more than one row found? Try this to return the content of row containing the first found cell:
Code: Select all
Clipboard := ""	
Send, ^c
Clipwait, 1
needle := clipboard 									
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"

xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
rw := xl.Cells.Find(needle,,,1).row
xl.rows(rw).entirerow.cells.copy
wrkbk.close(0)
xl.quit()
msgbox % clipboard
return
forget about that function.

Also please use the <code></code> tags and use the proper indentation (that makes reading it so much easier). Thks.

Thank you very much, i will try it, thanks again

Posted: **18 Mar 2022, 04:25**

flyingDman wrote: ↑
17 Mar 2022, 16:40
Will there possibly be more than one row found? Try this to return the content of row containing the first found cell:
Code: Select all
Clipboard := ""	
Send, ^c
Clipwait, 1
needle := clipboard 									
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"

xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
rw := xl.Cells.Find(needle,,,1).row
xl.rows(rw).entirerow.cells.copy
wrkbk.close(0)
xl.quit()
msgbox % clipboard
return
forget about that function.

Also please use the <code></code> tags and use the proper indentation (that makes reading it so much easier). Thks.

it works, thank you very much

you made my day

Posted: **18 Mar 2022, 13:39**

Further simplification. Just dawned on me

Code: Select all

rw := xl.Cells.Find(needle,,,1).row
xl.rows(rw).entirerow.cells.copy

can be replaced by this single line

Code: Select all

xl.Cells.Find(needle,,,1).entirerow.cells.copy

Posted: **19 Mar 2022, 06:26**

flyingDman wrote: ↑
18 Mar 2022, 13:39
Further simplification. Just dawned on me
Code: Select all
rw := xl.Cells.Find(needle,,,1).row
xl.rows(rw).entirerow.cells.copy
can be replaced by this single line
Code: Select all
xl.Cells.Find(needle,,,1).entirerow.cells.copy

Thanks very much!
I'm trying to add error handling (if the search value was not found), are these instructions correct?

Code: Select all

if (rw =""){
MsgBox, 0,Warning, No data could be found!.,5
}

Posted: **19 Mar 2022, 10:31**

I would try something like this:

Code: Select all

st := a_tickcount
Clipboard := ""	
Send, ^c
Clipwait, 0.2
needle := clipboard, clipboard := ""									
if !needle
	{
	msgbox Clipboard was empty
	exitapp
	}
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"
xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
found := xl.Cells.Find(needle,,,1)
if !found
	msgbox % "Nothing found `n" a_tickcount - st
else	
	{
	found.entirerow.cells.copy
	msgbox % clipboard "`n" a_tickcount - st
	}
wrkbk.close(0)
xl.quit()

return

Posted: **19 Mar 2022, 13:23**

flyingDman wrote: ↑

19 Mar 2022, 10:31

I would try something like this:

Code: Select all

st := a_tickcount
Clipboard := ""	
Send, ^c
Clipwait, 0.2
needle := clipboard, clipboard := ""									
if !needle
	{
	msgbox Clipboard was empty
	exitapp
	}
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"
xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
found := xl.Cells.Find(needle,,,1)
if !found
	msgbox % "Nothing found `n" a_tickcount - st
else	
	{
	found.entirerow.cells.copy
	msgbox % clipboard "`n" a_tickcount - st
	}
wrkbk.close(0)
xl.quit()

return

Thanks very much!
I think the time information is perfect, thank you for everything

AutoHotkey Community

Read a value from the clipboard, and look for this value in an Excel spreadsheet

Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet

Re: Read a value from the clipboard, and look for this value in an Excel spreadsheet