ToolTip with Excel Vlookup Function Topic is solved

[Telog]

Hello All,

I am very keen on writing a script that creates a message box at the cursor once text has been highlighted (then function pressed e.g. Ctrl+C). In that message box I would like it to display information about the text highlighted from an excel spreadsheet, assuming using the function of Vlookup to find said lines. I am struggling to find any sort of blog regarding the use of functions within a ToolTip script, ideally I would like it to look something like this:

Code: Select all

^C::
ToolTip, (ResultofVLookup1)n(ResultofVlookup2)n(ect,ect.)
Return

I might have over simplified it in my head, is there a way of creating an invisible search to create such a tool?
My mind will be blown if this is possible!

[Telog]

Good news, I've got it working how I had imiagined however I am only able to highlight/copy text from a word document or a body of an email. I am unable to complete the search using text from an email heading (outlook) or text from the internet. I am truly puzzled!

The script fails on line 8, xl.ActiveSheet.Range("A1").PasteSpecial(-4163)

Coding is as follows:

Code: Select all

$F1::

Send, {LCtrl Down}c{LCtrl Up}
var0 := clipboard

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("AHKform5.xlsx"), xl.Visible:=False
xl.ActiveSheet.Range("A1").PasteSpecial(-4163)
Xl.ActiveSheet.Range("A2").Copy 
Var1 := clipboard          

Xl.ActiveSheet.Range("A3").Copy 
Var2 := clipboard          

Xl.ActiveSheet.Range("A4").Copy 
Var3 := clipboard          

Xl.ActiveSheet.Range("A5").Copy 
Var4 := clipboard          

ToolTip,
(Join LTrim
 Account Number: %var0%`n
 Account Name: %var1%Risk Rating: %var2%Business Type: %var3%Revenue: %var4%
)

settimer, cleartt, -5000

Return

cleartt:
tooltip
Return

Furthermore...

xl.ActiveSheet.Range("A1").PasteSpecial(-4163)

If I change the above line to:

xl.ActiveSheet.Range("A1").PasteSpecial()

I am able to copy from the internet and email headings but now the script does not work for word documents and text on the body of an email!

[FanaticGuru]

Good news, I've got it working how I had imiagined however I am only able to highlight/copy text from a word document or a body of an email. I am unable to complete the search using text from an email heading (outlook) or text from the internet. I am truly puzzled!

The script fails on line 8, xl.ActiveSheet.Range("A1").PasteSpecial(-4163)

Coding is as follows:

Code: Select all

$F1::

Send, {LCtrl Down}c{LCtrl Up}
var0 := clipboard

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("AHKform5.xlsx"), xl.Visible:=False
xl.ActiveSheet.Range("A1").PasteSpecial(-4163)
Xl.ActiveSheet.Range("A2").Copy 
Var1 := clipboard          

Xl.ActiveSheet.Range("A3").Copy 
Var2 := clipboard          

Xl.ActiveSheet.Range("A4").Copy 
Var3 := clipboard          

Xl.ActiveSheet.Range("A5").Copy 
Var4 := clipboard          

ToolTip,
(Join LTrim
 Account Number: %var0%`n
 Account Name: %var1%Risk Rating: %var2%Business Type: %var3%Revenue: %var4%
)

settimer, cleartt, -5000

Return

cleartt:
tooltip
Return

Furthermore...

xl.ActiveSheet.Range("A1").PasteSpecial(-4163)

If I change the above line to:

xl.ActiveSheet.Range("A1").PasteSpecial()

I am able to copy from the internet and email headings but now the script does not work for word documents and text on the body of an email!

You probably do not need to be pasting and coping as much as you are from the clipboard. You can access and set the values of Excel cells directly.

Code: Select all

$F1::

Send, {LCtrl Down}c{LCtrl Up}

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("AHKform5.xlsx")
xl.ActiveSheet.Range("A1").Value := Clipboard

Var0 := xl.ActiveSheet.Range("A1").Value
Var1 := xl.ActiveSheet.Range("A2").Value
Var2 := xl.ActiveSheet.Range("A3").Value
Var3 := xl.ActiveSheet.Range("A4").Value
Var4 := xl.ActiveSheet.Range("A5").Value

ToolTip,
(Join LTrim
 Account Number: %var0%`n
 Account Name: %var1%Risk Rating: %var2%Business Type: %var3%Revenue: %var4%
)

settimer, cleartt, -5000

Return

cleartt:
tooltip
Return

I am not sure exactly how you are using this but you don't need to create a new instance and open the workbook every time. After you active the hotkey once, the workbook is already open.

Also, you are using xl.Visible:=False which is not needed as that is the default. But you are going to end up with a hidden Excel workbook running that your script never closes so you could end up with lots of zombie versions of Excel running in the background. I would probably just open it once at the very top of the script in the auto-execute section and then use OnExit to close the workbook when the script is exited.

FG

[Telog]

Works perfectly!
Thank you very much for the brilliant explanation and assistance
A2-A5 have formulas in linking to A1.