• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Intro to DOS & AutoHotkey AutoHotkey FAQ1 AutoHotkey FAQ2

My updated AutoHotkey Excel function library

Excel with AutoHotkeyOver the years I’ve done a lot in Excel.  Often I leverage my Excel function Library which is, at best, a work in progress.   I commissioned Maestrith, author of AHK Studio, to review it.   We worked through what did & didn’t work plus optimized a few things.

AutoHotkey Excel Function Library

AutoHotkey Webinar- Automating Outlook and COM

In our first hourAutoHotkey Bottle 2 of today’s  AutoHotkey webinar we talked about various ways you can automate MS Outlook.  We covered the below scripts:

  1. Creating an HTML email (Also see this video)
  2. Performing mail-merge on the above HTML email
  3. Integrating a mail-merge from an active Excel file  (Also see this video)
  4. Looping over emails in a folder
  5. Creating appointments in Outlook
  6. Creating an address book from looping over emails in Outlook

You can get the scripts here

The second hour dove a bit deeper into using Outlook and Excel.  We also talked through a few user’s questions.

Below is the information that was reviewed in the PowerPoint deck we reviewed:

Script Highlight:

Sift by FanaticGuru uses Regular Expressions and nGrams* to fuzzy match strings

*n-gram is a contiguous sequence of n items

COMComponent Object Model

  • What is COM?
  • Some of the programs COM integrates with:

Creating an HTML email in Outlook with AutoHotkey

  1. Connect to Outlook or Create a new one (but may not deploy right then)
  2. Create a New Mail Item object
  3. Provide values for properties
    1. Specify Account Sending from
    2. Type of email (HTML, RTF, Text)  (Search here for Outlook Constants)
    3. Identify Recipients: To, CC, BBC
    4. Subject line
    5. HTML Body
    6. Attachments
    7. Delivery time (Fuse)
    8. Request Delivery Reciept
    9. Importance
    10. Display / Send
  4. Convert above email to mail-merge with names & email addresses

AutoHotkey Bottle 4

Resources

AHK Forum:

MSDN resources:

Outlook examples on the-Automator

Stack Overflow

AutoHotkey webinar on COM & Excel

Use Variables/Parameters to pass Methods or Properties in COM w/AutoHotkey

Black Bottle 2I had wanted to be able to make a function where I pass Methods or Properties in COM.  (this makes it much more flexible and eases updating / maintaining of code.)  Unfortunately it was not as straight-forward as I’d hoped.

Thankfully maestrith, Author of AutoHotkey Studio, had already solved this problem and shared the solution with me!  The code which shows how to pass Methods or Properties in COM is listed below.  While in the video I demonstrate connecting to IE and perform web scraping, the solution is equally applicable to any COM object like Excel, etc.

pwb := WBGet()
Property:="OuterHTML"

Get_ID(pwb,"site-description","OuterHTML")

Get_ID(PXL,ID,Property){
ID_Var:= PXL.document.GetElementByID(ID)
MsgBox % ID_Var[Property]
}
;~  MsgBox % pwb.document.GetElementByID("site-description").OuterHTML
;~  MsgBox % pwb.document.GetElementByID("site-description").InnerHTML

return


pwb := WBGet()
MsgBox % WebScraping_Get(pwb,"class","widget_tag_cloud","0","")
MsgBox % WebScraping_Get(pwb,"id","tag_cloud-3",,"oh")

;***********Getting data from page*******************
WebScraping_Get(PXL,Method,Thing,Index="",Property="OuterHTML"){
;***********Set Methods and Properties to static so do not have to rebuild each time it is called*******************
static Methods:={id:"getElementByID",class:"getElementsByClassName",tag:"getElementsByTagName",name:"GetElementsByName"}
static Properties:={it:"InnerTEXT",ot:"OuterText",ih:"InnerHTML",oh:"OuterHTML",vl:"Value",si:"selectedIndex",ck:"checked"}
Doc:= PXL.document
if (method !="id")
      return Doc[Methods[Method]](Thing)[Index][Properties[Property]] ;Arrays
else
      return Doc[Methods[Method]](Thing)[Properties[Property]] ;Not an Array
}
return

White sticker on computer 4

Demonstration Video to pass Methods or Properties in COM

AutoHotkey and Excel Worksheet functions: Mean/Average, Median & Mode

Excel worksheet functionsWhite sticker on computer 4Typically I like to use AutoHotkey to insert the formula into Excel.  This allows for the user to make changes (or have it update if the  data changes).   Sometimes I just want a given value based on the data at-hand.  This is when it is awesome to be able to call the Excel worksheet functions.  In the below code and video I demonstrate how to call three Summary variables of central tendency: Mean (Average), Median and Mode.   For normally distributed data these will be the same however, in the real world, data is often skewed / non-bell shaped (has kurtosis).  Make sure you review the data to decide which best “summarizes” the data.

In the video I show how I initially planned to create three separate functions (one each summary variable) however with some help from Maestrith (author of AHK Studio) I was able to write one function and pass the method as a parameter.  🙂AutoHotkey Bottle 3

Here is the script for Excel worksheet functions

XL_Handle(XL,1)

MsgBox % "median " XL_WF_Averages(XL,"A1:H1","Median")
MsgBox % "average " XL_WF_Averages(XL,"A1:H1","Average")
MsgBox % "mode " XL_WF_Averages(XL,"A1:H1","Mode")
;***********Delete blank columns*******************
XL_WF_Averages(PXL,RG,Summary_Var){
	app:=PXL.Application.WorkSheetFunction
	Try val:=app[Summary_Var](PXL.Application.ActiveSheet.Range(RG))
return val
}

;***********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)
}

And here is the video where I walk through Excel worksheet functions