In this video I demonstrate a new function I added to my AHK Excel Library. VLookups are really cool! In the below video I demonstrate how to use the function. Note, near the end I tried turning off the “exact match”. This doesn’t create a “fuzzy” match, it looks for the next highest value. (Probably something you should be very careful using…)
Category: Excel
How I used Google Translate to save the client both time and money
In this session of “what I’ve automated with AutoHotkey” I explain how I saved my client a ton of time and money by using the Google Translate API.
How I used Google Translate to save the client both time and money
Below is the code I used in the code to Google Translate to save the client both time and money. Make sure you put my Excel function library and Maestrith’s Notify function in your library.
#SingleInstance,Force Global API_Key,ID,All,From,To,XL,EndPoint,LangObj Global Column_to_Translate:="A", Column_To_Put_Translations:="B" ;************************************** EndPoint:="https://www.googleapis.com/language/translate/v2" ; ?parameters IniRead, API_Key,Auth.ini,API, Key ;read current token (You'll need to get your own) XL:=XL_Handle(1) if ! IsObject(Xl) { ;Joe added 9/22 to make sure Excel is loaded before running MsgBox % "Please Start Excel then click this box" Reload } LangObj:={"Chinese Trad":"zh-CN","Chinese Simp":"zh-TW",Italian:"it",Czech:"cs",French:"fr",German:"de",Hebrew:"he",Indonesian:"id",Japanese:"ja",Korean:"ko",Malay:"ms",Polish:"pl",Portuguese:"pt",Russian:"ru",Spanish:"es",Thai:"th",Turkish:"tr",Vietnamese:"vi",English:"en"} for a in LangObj ;Build Language object for ComboBox LangList.=a "|" All:=[] gui,+hwndMain ID:="ahk_id" Main gui,Add,ListView,w800 h300,Text gui,Add,ComboBox,vFrom,% Trim(LangList,"|") ;~ https://cloud.google.com/translate/docs/languages gui,Add,ComboBox,x+m vTo,en||it|es|la ;Languages to translate into gui,Add,Text,x+60, Column to be translated gui,Add,ComboBox,x+m w50 vColumn_to_Translate,A||B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z ;Store which column to translate gui,Add,Text,x+60, Destination for translations gui,Add,ComboBox,x+m w50 vColumn_To_Put_Translations,A|B||C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z ;Store where to put the translations gui,Add,Button,xm gLang,Discover Language gui,Add,Button,x+25 gTranslate,Translate gui,Add,Button,xm gRefresh,Refresh Refresh() gui,Show return Refresh(){ Rows:=XL.ActiveSheet.UsedRange.Rows.Count,LV_Delete(),All:=[] while(A_Index<=Rows){ if(Value:=(Obj:=XL.Range(Column_to_Translate . A_Index)).Value) All.Push(Obj),LV_Add("",Value) if(All.MaxIndex()=15) Break }LV_Modify(1,"Select Vis Focus") } Translate(){ Gui,Submit,NoHide From:=LangObj[From]?LangObj[From]:From if(!From||!To) return m("Both Language values must be set") XL.Columns(Column_To_Put_Translations).ColumnWidth:=100 Rows:=XL.ActiveSheet.UsedRange.Rows.Count Blank:=0,BlankInput:=0 while(A_Index<=Rows){ if(!(Obj:=XL.Range(Column_To_Put_Translations . A_Index)).Value){ Blank:=0 t("Processing") if(Value:=XL.Range(Column_to_Translate . A_Index).Value){ QueryString:=QueryString_Builder({"source":From,"target":To,"q":Value,"key":API_Key}) RegExMatch(Val:=API_Call(EndPoint,queryString),"OUi)\x22translatedText\x22:\s+\x22(.*)\x22",Found) Text:=Found.1 while(RegExMatch(Text,"OU)(\&#(\d+);)",Found)) Text:=RegExReplace(Text,"\Q" Found.1 "\E",Chr(Found.2)) while(RegExMatch(Text,"OU)(\")",Found)) Text:=RegExReplace(Text,"\Q"\E",Chr(34)) Obj.Value:=Text } }else{ Blank++ m(Blank) if(Blank=5) Break } } t(),Notify(60).AddWindow("Complete",{size:14,Animate:"Center",ShowDelay:1000,Icon:161,IconSize:15,Title:"Translations",TitleSize:14,Color:"0x00FF00",TitleColor:"0xFF0000",time:6000}) } GuiEscape: GuiClose: ExitApp return ;********************Language detection*********************************** Lang(){ Lang:=API_Call("https://translation.googleapis.com/language/translate/v2/detect",QueryString_Builder({q:All[LV_GetNext()].Value,key:API_Key})) RegExMatch(Lang,"OUi)\x22language\x22:\s+\x22(.*)\x22",Found) for a,b in LangObj if(b=Found.1) Language:=a ControlSetText,ComboBox1,% Language?Language:Found.1,%ID% } ;********************API Call*********************************** API_Call(EndPoint,queryString){ static WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1") WebRequest.Open("GET", EndPoint . queryString) WebRequest.SetRequestHeader("Accept", "text/xml;charset=UTF-8") WebRequest.Send() return Response:=WebRequest.ResponseText } ;********************URI Encode charachters*********************************** UriEncode(Uri, full = 0){ oSC := ComObjCreate("ScriptControl") oSC.Language := "JScript" Script := "var Encoded = encodeURIComponent(""" . Uri . """)" oSC.ExecuteStatement(Script) encoded := oSC.Eval("Encoded") Return encoded }
Using a Safe Array in Excel with AutoHotkey
In the below video I show you how you can use a Safe Array in Excel.
You can read a bit more about them here on the AutoHotkey forum
Using a Safe Array in Excel with AutoHotkey
SetBatchLines,-1 #SingleInstance,Force ;********************Excel Safe array*********************************** XL:=XL_Handle(1) ;Connect to Excel sArr :=xl.Range["a1:L29"] ;Store the content of the array into sArr ;~ MsgBox % IsObject(sArr) for row in sArr for cell in row data.= cell.value "," MsgBox % Data ;If you want to access it directly, add the .Value sArr :=xl.Range["a1:L29"].Value MsgBox % sArr[2,2]
AutoHotkey Webinar- Using AHK string functions in Excel
AHK In this webinar we walked through using AutoHotkey String functions in Excel.
Script Highlight: Pull data about your computer with WMI by shajul. Forum post and my link to the zipped files of the separate scripts I wrote
Video Hour 1 & 2 : AHK string functions in Excel
- Webinar on Excel and COM
- Excel and AHK Tutorials
- Joe’s Excel Function Library
- Excel Function Library from Tre4shunter
Books on RPA (Robotics Process Automation) that I mentioned in the webinar
Below are links to books I showed during the webinar. I put them into two groups (those I like/recommend and those I don’t). I’m more of a “glass is half-full” kind of guy and think technology is good. The books I don’t recommend were much more negative towards technology. While I don’t recommend them, I think time will tell who is right/wrong. Personally any of the following you buy I’d try and buy as used. Many are pretty expensive new (probably because of the small market size)
Recommended Robotics Process Automation books
Service Automation: Robots and the Future of Work 2016 -Great book!
Robotic Process Automation and Risk Mitigation: The Definitive Guide 2017
Robotic Process and Cognitive Automation: The Next Phase 2018 -Great book!
The Singularity Is Near: When Humans Transcend Biology 2019 – very interesting
Life After Google: The Fall of Big Data and the Rise of the Blockchain Economy 2018 – some wild ideas here
The Human Advantage: The Future of American Work in an Age of Smart Machines 2018
The Glass Cage: How Our Computers Are Changing Us 2015
Managing IT as a Strategic Resource 1997
Beyond the IT Productivity Paradox 1999 – old but still interesting
Not recommended but still on the RPA topic
In The Age Of The Smart Machine: The Future Of Work And Power 1989
The Lights in the Tunnel: Automation, Accelerating Technology and the Economy of the Future 2009
A Dangerous Master: How to Keep Technology from Slipping Beyond Our Control 2015
Rise of the Robots: Technology and the Threat of a Jobless Future 2016
We also mentioned using ternary operators
- Intro to Ternary tutorial by Joe
- Ternary walk through by VxE
- Not Ternary, but you might like one line operators
and Alternate Data Stream as data storage
Here’s the code Jackie walked through.
#SingleInstance,Force username := "Jackie" msgbox % "Name: " username "`nPassword: " password() return password(){ FilePath := A_ScriptFullPath IniRead, Var, %FilePath%:Stream:$DATA, Settings, Pass, error If (Var="error"){ InputBox, Var , New, Please enter password, HIDE IniWrite, %Var%, %FilePath%:Stream:$DATA, Settings, Pass } return Var }