How to call a vlookup from AutoHotkey? Topic is solved

[Avastgard]

I want to use an Excel spreadsheet to structure data that will be retrieved and pasted by an AutoHotkey script.

In other words, I want to tell an AutoHotkey script to take a certain value given by the user (maybe typing it in an inputbox or GUI), look it up in a spreadsheet and return the value in the same row 2 columns over to the right (in a MsgBox or pasting it in the text), all of this without opening the spreadsheet. Basically a VLookup.

I know how to do this without using the sheet, but in my use case, other people will update the database in the sheet and having the script retrieve the info from there would be ideal instead of people messing with the script.

Edit: Any tips on where to start with this?

[flyingDman]

try with rangelookup := 0
BTW your posting in the wrong section (this is v1 code) and you're yet again confirming that AI generated code is not up to snuff yet(and against forum rules).

[boiler]

Moved topic from the v2 section.

@Avastgard - Please don't post AI-generated code in order to get help correcting it as that is against the forum rules.

[Avastgard]

Oops, sorry guys. I remember reading this rule a while ago, but completely forgot about it. I deleted the AI-generated code from my question.

@boiler I honestly have no idea whether v1 or v2 are better suited for this use case. Now that the post has no code does it make a difference where it is?

[boiler]

You should post in the section for whichever version you want to use so helpers know which version to use in their replies. Neither one is better suited except in cases where a library exists for one but not the other, which wouldn't be the case here. If you have no other reason, always choose v2. Do you want me to move this thread to the v2 section?

[Avastgard]

Do you want me to move this thread to the v2 section?

I would appreciate that. Thank you.

[flyingDman]

This brings out an interesting difference between V1 and V2. Using range_lookup := false in v1 triggers the 0x800A03EC error while range_lookup := 0 does not (and finds the expected result) while in v2 both false and 0 are fine.
v1:

Code: Select all

needle := "matrix"
xl := ComObjCreate("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\xxx\Documents\Spreadsheets\vlookuptest.xlsx")
range_lookup := 0			; := false is not OK
msgbox % Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup)
Workbook.close(0)
xl.quit

v2:

Code: Select all

needle := "matrix"
xl := ComObject("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\xxx\Documents\Spreadsheets\vlookuptest.xlsx")
range_lookup := 0			; := false is OK
msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup)
Workbook.close(0)
xl.quit

BTW I typically avoid Vlookup and either use something like:

Code: Select all

for c in xl.range("a1:b23")
	if (c.value = needle)
		msgbox % c.offset(0,1).value

Which finds multiple results (if there are more than 1) or in case of a large spreadsheet. I will save the whole range to a safearray and search the safearray. The latter is much faster.

[Avastgard]

Thanks for the code, @flyingDman.

However, I run into the same error message with the v2 version:

Code: Select all

Error: (0x800A03EC) 
Unable to obtain VLookup property from class WorksheetFunction
Source:	Microsoft Excel

Specifically: VLookup

	005: Workbook := xl.Workbooks.Open("C:\Users\flima\Desktop\lista CNPJs KBI.xlsx")
	006: range_lookup := 0
▶	007: msgbox(Xl.WorksheetFunction.VLookup(needle, xl.range("a1:b23"), 2, range_lookup))
	008: Workbook.close(0)
	009: xl.quit()

Call stack:
C:\Users\flima\AutoHotkey\Excel\Excel paste vlookup.ahk (4) : [] xl := ComObject("Excel.Application")
> Auto-execute

Also, how do I specify which sheet should be looked up?

[RussF]

FWIW, I created a spreadsheet that matches the guidelines in @flyingDman's V2 example: name - vlookuptest.xlsx, 2 columns, 23 rows, cells in column A were junk text except for one entry "matrix" and just sequential integers in column B. I also changed the path in his code to point to my file.

As long as all the criteria were as listed above, it worked perfectly. If I changed the range, or changed "matrix" to something else, or pointed to a nonexistent file - basically anything that would case the lookup to fail, I got the same error code, albeit with different text description.

Make sure everything matches correctly. You may want to try his second alternative as well - less prone to throwing Excel errors.

Russ

[flyingDman]

Google error 0x800A03EC and see what could be the issue. There are a number of potential problems but they are likely related to Excel and Windows rather than AHK. Have you been able to run other scripts with COM for Excel?
To access a different sheet use this:

Code: Select all

xl.worksheets("sheet2").range("a1:b23")

Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AY
Thanks @RussF for testing!

[Avastgard]

FWIW, I created a spreadsheet that matches the guidelines in @flyingDman's V2 example: name - vlookuptest.xlsx, 2 columns, 23 rows, cells in column A were junk text except for one entry "matrix" and just sequential integers in column B. I also changed the path in his code to point to my file.

As long as all the criteria were as listed above, it worked perfectly. If I changed the range, or changed "matrix" to something else, or pointed to a nonexistent file - basically anything that would case the lookup to fail, I got the same error code, albeit with different text description.

Make sure everything matches correctly. You may want to try his second alternative as well - less prone to throwing Excel errors.

Russ

Thank you for the tips, @RussF. I made a sheet from scratch and the code provided by @flyingDman worked.

Google error 0x800A03EC and see what could be the issue. There are a number of potential problems but they are likely related to Excel and Windows rather than AHK. Have you been able to run other scripts with COM for Excel?
To access a different sheet use this:

Code: Select all

xl.worksheets("sheet2").range("a1:b23")

Also watch this: https://www.youtube.com/watch?v=ZI5FLSll9AY
Thanks @RussF for testing!

@flyingDman, thank you for the very informative video. I was actually aware that there are better alternatives to VLookup, like Index Match and XLookup, but I figured I would get more responses if asked about VLookup.

The code below works on a brand new spreadsheet:

Code: Select all

#Requires Autohotkey v2.0+
#SingleInstance force
needle := "matrix"
xl := ComObject("Excel.Application")
Workbook := xl.Workbooks.Open("C:\Users\username\AutoHotkey\Excel\vlookuptest.xlsx")
xl.worksheets("test").range("a:b")
range_lookup := 0			; := false is OK
msgbox xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
Workbook.close(0)
xl.quit

However, I realized that this won't work for me because I need an easy way to input the lookup value (column A) and have the output value (column B) show up on a MsgBox or be pasted on the text. I tried adding a hotkey to trigger an InputBox to store the input as a variable, but this is a little too advanced for me and v2 syntax is killing me. How can I dynamically replace the value of needle with user input?

[flyingDman]

Inputbox with F12 as hotkey:

Code: Select all

f12::
	{
	needle := InputBox("Lookup value?", "")
	needle := needle.value						;instead of needle := "matrix"
	xl := ComObject("Excel.Application")    	;v1 --->  xl := ComObjCreate("Excel.Application")
	Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx")
	range_lookup := 0
	msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
	Workbook.close(0)
	xl.quit
	}

[RussF]

@flyingDman, you name both the InputBox object and the result string "needle". The next (and only) reference to "needle" is to its string form, which is the last thing you assigned to it. I'm just curious, if you needed to refer back to the object, would AHK be able to differentiate between the two by context?

I never give two different variable types the same name just to avoid such ambiguities.

Russ

[boiler]

The object doesn’t exist anymore after he replaces it with the value property’s contents (a string), so there’s no ambiguity. The variable needle is only a string with the user’s input after that point, which is what he wants. He is eliminating the object because he knows he is not going to make use of the other property of the object — the button that was clicked.

[RussF]

Thanks @boiler, after all this time using AHK, I still have the "avoid the type mismatch error" syndrome, even though AHK has no problem with it.

Russ

[Avastgard]

Inputbox with F12 as hotkey:

Code: Select all

f12::
	{
	needle := InputBox("Lookup value?", "")
	needle := needle.value						;instead of needle := "matrix"
	xl := ComObject("Excel.Application")    	;v1 --->  xl := ComObjCreate("Excel.Application")
	Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx")
	range_lookup := 0
	msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
	Workbook.close(0)
	xl.quit
	}

Awesome, thank you very much, @flyingDman.

[Avastgard]

Inputbox with F12 as hotkey:

Code: Select all

f12::
	{
	needle := InputBox("Lookup value?", "")
	needle := needle.value						;instead of needle := "matrix"
	xl := ComObject("Excel.Application")    	;v1 --->  xl := ComObjCreate("Excel.Application")
	Workbook := xl.Workbooks.Open("C:\Users\xxxx\Documents\Spreadsheets\aaaa.xlsx")
	range_lookup := 0
	msgbox Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
	Workbook.close(0)
	xl.quit
	}

Awesome, thank you very much, @flyingDman.

Would it be possible to send the result of line 8's Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) to clipboard and then paste it? I tried Clipboard := Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup), but it just literaly pastes Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup) on the text.

[RussF]

In V2, the clipboard name is A_Clipboard

Russ

[Avastgard]

In V2, the clipboard name is A_Clipboard

Russ

Thanks again! Here is the final code that will paste the VLookup result instead of displaying it on a MsgBox and allows you to choose the sheet to lookup:

Code: Select all

#Requires Autohotkey v2.0+
#SingleInstance force

F10::
	{
	needle := InputBox("Type Lookup value", "", "w200 h100")
	needle := needle.value						;instead of needle := "matrix"
	xl := ComObject("Excel.Application")    	;v1 --->  xl := ComObjCreate("Excel.Application")
	Workbook := xl.Workbooks.Open("C:\Users\username\AutoHotkey\Excel\spreadsheet.xlsx")
	xl.worksheets("Sheet1").range("a:b")
	range_lookup := 0
	A_Clipboard := Xl.WorksheetFunction.VLookup(needle, xl.range("a:b"), 2, range_lookup)
	Workbook.close(0)
	xl.quit
	Send "^v"
	return
	}