• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

AutoHotkey Webinar- Using AHK string functions in Excel

webnar 300x200AHK In this webinar we walked through using AutoHotkey String functions in Excel.

Script Highlight:  Pull data about your computer with WMI by shajul.

Video Hour 1 & 2 : AHK string functions in Excel

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

and Alternate Data Stream as data storage

Here’s the code Jackie walked through.

 

AutoHotkey webinar: Getting text from programs with AutoHotkey

webnar 300x200

Video Hour 1:  High Level: Getting text from programs

General ways (there are more) to get text & the order to try them:

  1. Using COM to extract text from programs like: Excel, Word, PowerPoint, Outlook, IE
  2. Getting Text from PDFs
  3. Using Active Accessibility Viewer & UI Automation
  4. ControlGet
  5. Copy / Paste (when all else fails or when just need a quick solution)Squishy Ball 2
  6. OCR – Optical Character Recognition

Video Hour 2: Coding and Q&A

Script Highlight: Chrome.ahk by GeekDude

  • Automate Chrome without Selenium
  • Not as robust as COM w/IE but a great start at basic automation
  • Connect to running Chrome (running in Debug mode)
  • Page navigation
  • Get / Set elements & JavaScript Injection
  • Print pages to PDF
  • Capture to Screenshot
  • Some tutorial videos

Resources for getting text from programs

COM– Microsoft’s Component Object Model

Extracting from PDFs

Active Accessibility Viewer & UI Automation

Active Accessibility & UI Automation are built-in Microsoft architecture that can allow you to, programmatically, get text from programs

Let’s see a few examples using:

AutoHotkey Bottle 3

ControlGet

On standard Windows controls, you can access them with ControlGet

  • Some things like listboxes, listviews, etc. have some pretty cool capabilities
  • Often newer programs do not use the standard window controls 🙁

OCR – Optical Character Recognition

 

Excel & AutoHotkey-Replacing Null values in Excel

Replacing Null values in ExcelAutoHotkey Bottle 2When some programs export data they will insert #Null! into a cell to indicate there was no data (SPSS to name one).  While I would rather have something than nothing, when trying to compute averages or share data with clients the #Null! value is not ideal.  🙁  In this tutorial I demonstrate how easy it is to Replacing Null values in Excel with AutoHotkey.

The following video demonstrates Replacing Null values in Excel.  While I didn’t show the entire code in the video, it is below for your reference.

AutoHotkey Merchandise-White Stress ball

AutoHotkey Function for Replacing Null values in Excel

;***********get handle*******************
XL_Handle(XL,1) ; pointer to Application   XL_Handle(XL,2) Pointer to Workbook

;***********call function *******************
XL_Replace_Null(XL)
return

;***********replace Null*******************
XL_Replace_Null(PXL,RG:=""){
if (! RG)
    RG:=XL_Used_RG(PXL,0)
PXL.Range(RG).Replace("#NULL!","")  ;
}


;***********************Used Range********************************.
;~ XL_Used_RG(XL,Header:=1) ;Use header to include/skip first row
XL_Used_RG(PXL,Header=1){
IfEqual,Header,0,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL) ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)
IfEqual,Header,1,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL)+1 ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)
}

;***********************First row********************************.
;~ XL_First_Row(XL)
XL_First_Row(PXL){
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(1).Row
}

;***********************Last Row********************************.
;~ XL_Last_Row(XL)
XL_Last_Row(PXL){
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Count).Row
}
XL_Last_Col_Alpha(PXL){
LastCol:=XL_Last_Col_Nmb(PXL)
IfLessOrEqual,LastCol,26, Return, (Chr(64+LastCol))
	Else IfGreater,LastCol,26, return, Chr((LastCol-1)/26+64) . Chr(mod((LastCol- 1),26)+65)
}

XL_Last_Col_Nmb(PXL){
Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column
}
;***********************First Column Alpha**********************************.
;~ XL_Last_Col_Alpha(XL)
XL_First_Col_Alpha(PXL){
FirstCol:=PXL.Application.ActiveSheet.UsedRange.Columns(1).Column
IfLessOrEqual,LastCol,26, Return, (Chr(64+FirstCol))
	Else IfGreater,LastCol,26, return, Chr((FirstCol-1)/26+64) . Chr(mod((FirstCol- 1),26)+65)
}

;***********handle*******************
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
    If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
    Return	ComObjEnwrap(9,pacc,1)
}

 

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey