Excel Tutorials and XL Function library

Helpful script writing tricks and HowTo's
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Excel Tutorials and XL Function library

24 Mar 2019, 09:06

Below I have a lot of tutorials using Excel & AutoHotkey. You might like to check out my AutoHotkey Excel Function library. While it isn’t all perfect, there is a lot that can simplify your life!
  1. Create / Connect to Excel with AutoHotkey via COM
  2. AutoHotkey and Excel Object model: Application, Workbook, Worksheet
  3. Shading cells with AutoHotkey via COM
  4. Obtain First row, Last row, # Used rows
  5. Detecting Used Range (first column, last column, first row, last row, etc.)
  6. Obtain First / Last Column & numeric to string converter
  7. Setting Horizontal and Vertical cell alignment
  8. Simplify writing AutoHotkey code using the Macro recorder & Excel constants
  9. Manipulate columns (Insert, Delete, set width)
  10. Insert, Delete, set Height of Rows
  11. Freeze Panes, Toggle screen updating, Inject file name into MRU
  12. Merging, Shrinking, and Wrapping cells
  13. Set cell Number format
  14. Font size, type, bold, italic, underline
  15. Adding / Removing / Changing Borders in Excel
  16. Creating “pretty link” for email merge from data in cells
  17. Using Offset and a range to iterate over cells and create a “better” email merge
  18. Finding & Returning specific header locations (for use in Filtering, Sorting, Merging, etc.)
  19. Clear Excel: Content, format, Data, Notes, comments, etc.
  20. Leverage built-in Excel worksheet functions & pass a method as a parameter (simplify code maintenance)
  21. Renaming, Moving, Activating, and Changing the color of Worksheets / Tabs
  22. Delete an entire Column or Row based on a Value
  23. Easily Loop over Cells in Excel by using a For Loop in AutoHotkey. I also demonstrate how to detect selection area
  24. Copy a range to the clipboard or a variable from Excel
  25. Various ways to Paste in Excel (Think of Paste Special but progromatically doing it)
  26. Selecting a Cell / Range of Cells
  27. Inserting and Deleting Comments / Notes
  28. Inserting and Deleting Worksheets with Excel & AutoHotkey
  29. Inserting and Deleting Hyperlinks
  30. Creating personalized emails. Also see our Outlook webinar where we use Excel with Outlook
  31. Search & Replace values
  32. Multiple Search / Replace values. Also delete #Null!
  33. Find value and return location (Also allows for finding the nth instance of it)
  34. Find multiple text values in Header row and return their locations
  35. Deleting blank columns and/or Rows
  36. Sorting Columns in Excel
  37. Sorting Rows
  38. Remove Duplicate Rows
  39. Setting and Filtering data in Excel
  40. Get and Set value on a specific worksheet
  41. Connect to or Launch Excel. Also how to change it’s visibility and Close workbook and Quit Excel
  42. Opening various file types (XLS, XML, CSV, TSV/TXT, XLSX, and HTML) in Excel
  43. Saving Excel files in various file types (CSV, Tab Delimited, XLSX)
:dance: At the bottom of my Excel page I have several cool "real-world" examples of automating Excel.
Last edited by Joe Glines on 29 Nov 2020, 08:42, edited 4 times in total.
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: Excel Tutorials and XL Function library

08 Apr 2019, 11:05

@Joe Glines, thanks for putting links to all your excellent materials together in one spot.
Regards,
burque505
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

09 Apr 2019, 09:31

burque505 wrote:
08 Apr 2019, 11:05
@Joe Glines, thanks for putting links to all your excellent materials together in one spot.
Regards,
burque505
@burque505 You bet! Let me know if there's something you're trying to do in Excel but can't find
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Sdamico
Posts: 1
Joined: 28 Jun 2019, 07:55

Re: Excel Tutorials and XL Function library

28 Jun 2019, 08:00

This is so helpful!
Do you also have a tutorial on running pivot tables via autohotkey?
Thanks
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

08 Jul 2019, 16:12

@Sdamico Sorry, I didn't see this earlier. No, I haven't done those but I did do an amazing amount of reporting with AutoHotkey and Excel... Basically I was using formulas to look at data and aggregate the values.

You can check it out here
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
JKnight_xbt33
Posts: 135
Joined: 18 Sep 2019, 02:06

Re: Excel Tutorials and XL Function library

25 Dec 2019, 09:22

Thanks for your excel library @Joe Glines , Do you have instructions on getting it work?

When i try to run a script containing one of the functions I get an error saying they are nonexistant.

I know there's something simple missing.

Thanks for your help.
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

21 Jan 2020, 10:24

JKnight_xbt33 wrote:
25 Dec 2019, 09:22
Thanks for your excel library @Joe Glines , Do you have instructions on getting it work?

When i try to run a script containing one of the functions I get an error saying they are nonexistant.

I know there's something simple missing.

Thanks for your help.
You'd need to actually tell me what you tried to run...
Sorry for the late response. I'm not in front of the computer on a daily basis and don't get any notifications from the forum.
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Fleurpink
Posts: 1
Joined: 20 Jan 2020, 06:03

Re: Excel Tutorials and XL Function library

21 Jan 2020, 15:04

Thank you a lot for your help, there's no less people now who share their knowledge
Last edited by Fleurpink on 23 Jan 2020, 16:06, edited 1 time in total.
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

22 Jan 2020, 10:45

Fleurpink wrote:
21 Jan 2020, 15:04
This page has a bunch of videos walking through how to use my Excel library
https://the-automator.com/excel-autohotkey/
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Axxi
Posts: 15
Joined: 02 Apr 2020, 10:56

Re: Excel Tutorials and XL Function library

14 Jun 2020, 08:03

Hi Joe!

Thank you so much for sharing your knowledge with us. I was just wondering if there is a quick fix to make the following function also look for case sensitive?

XL_Search_Replace_Multiple

The single search and replace (XL_Search_Replace), works great for this, but was wondering if the above function also could have this implemented in any way?

Thank you in advance!
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

15 Jun 2020, 06:07

@Axxi that is something you can figure it... I'm not using the Excel search/replace so it is just adding some AutoHotkey code / logic
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
nik2502
Posts: 4
Joined: 15 Jun 2020, 09:00

Re: Excel Tutorials and XL Function library

15 Jun 2020, 09:07

Hi Joe,

Thanks a lot for the all info. and knowledge on automating Excel with AHK. Was wondering if there's a way to add 'Data Validation' to add a custom drop-down list of values in a specific column. Searched a lot for this but couldn't find anything helpful.
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

16 Jun 2020, 13:58

@nik2502
I don't use it (nor do I think many do). but, yes, sure should be able to. I just tried to record a macro in Excel (with the Excel macro recorder) and it captures the code for setting data validation. You can do the same thing, and add it to your version
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Tre4shunter
Posts: 139
Joined: 26 Jan 2016, 16:05

Re: Excel Tutorials and XL Function library

14 Jul 2020, 09:51

Hey Joe -

Very nice and complete library here! Just a few additions maybe someone will find useful:

Will require XL_Check() Function as well as some global constants if used as part of an included LIB. Otherwise, you can easily modify the functions to work with other methods.

Code: Select all

GLOBAL		xlup 					= -4162
GLOBAL		xldown 					= -4121
GLOBAL		xlLeft 					= -4159
GLOBAL		xlRight 				= -4161
GLOBAL		xlFormulas 				= -4123
GLOBAL		xlValues 				= -4163
GLOBAL		xlCenter 				= -4108
GLOBAL		xlCalculationAutomatic 	= -4105
GLOBAL		xlCalculationManual 	= -4135
GLOBAL		xlAscending				= 1
GLOBAL		xlDescending			= 2
GLOBAL		xlSortOnValues			= 0
GLOBAL		xlWhole 				= 1
GLOBAL		xlPart 					= 2
GLOBAL		xlPrevious 				= 2
GLOBAL		xlNext 					= 1
GLOBAL		xlByrows 				= 1
GLOBAL		xlByColumns 			= 2
GLOBAL		xlYes 					= 1
GLOBAL		xlNo 					= 2

; Excel_Get by jethrow (modified)
; https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
XL_Check(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
	static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
	WinGetClass, WinClass, %WinTitle%
	if !(WinClass == "XLMAIN")
		return "Window class mismatch."
	ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
	if (ErrorLevel)
		return "Error accessing the control hWnd."
	VarSetCapacity(IID_IDispatch, 16)
	NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
	if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
		return "Error calling AccessibleObjectFromWindow."
	window := ComObject(9, pacc, 1)
	if ComObjType(window) != 9
		return "Error wrapping the window object."
	Loop
		try return window.application
	catch e
		if SubStr(e.message, 1, 10) = "0x80010001"
			ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
	else
		return "Error accessing the application object."
}
Functions:

Code: Select all

/*
;XL_RangeFind() Function - Thanks Tidbit ;)
;=========================================
;Params:
;
;Note - This function returns ONE result, dependng on how you set the parameters up
;		If you want to return all results found within a range, use XL_RangeFindAll
;
;Name - Specify "" if you want to retrive handle to active excel workbook
;	Otherwise, use the full name of the workbook you want a handle to - e.g. "Book1.xlsx - Excel"
;
;Sht - Specify "" if you want to use the active sheet, otherwise use the Sheet Name e.g. "Sheet1"
;
;Range - Range representing where you want to 'Fnd' the value e.g. "A1:A100"
;
;What -Pretty straight forward...what are you searching for?
;
;After - You you must it where to start e.g. "A30"
;		This will affect the Search Order Param - See the MSDN for details
;		I normally use the last cell in my rang to search from, otherwise in some cases you may miss the first result
;
;LookIn - One of the options XlValues, or XlFormulas
;
;Lookat - One of the values xlwhole, or xlPart
;
;Order - xlbyrows, or xlbycolumns
;
;Direction - xlnext or xlprevious
;
;Dtype - OAny of the following as text string: Value,Text,Formula,Address,Row,Column
;
;=========================================
*/
XL_RangeFind(Name,Sht,Range,What, After, LookIn,LookAt,Order,Direction,Dtype) {
	xla := XLCheck(Name)
	if xla = False
		return
	sht := ((sht="") ?  xla.activesheet.name : sht) 
	
	return xla.sheets(Sht).range(Range).Find(What,xla.sheets(Sht).range(After),LookIn,Lookat,Order,Direction)[Dtype]
}

/*
;XL_RangeFindAll() Function
;=========================================
;Params:
;
;Note - This function returns ALL Results in Specified Range
;		
;Name -Specify "" if you want to retrive handle to active excel workbook
;	Otherwise, use the full name of the workbook you want a handle to - e.g. "Book1.xlsx - Excel"
;
;Sht -Specify "" if you want to use the active sheet, otherwise use the Sheet Name e.g. "Sheet1"
;
;Range-Range representing where you want to 'Fnd' the value e.g. "A1:A100"
;
;What -Pretty straight forward...what are you searching for?
;
;After - You you must it where to start e.g. "A30"
;		This will affect the Search Order Param - See the MSDN for details
;		I normally use the last cell in my rang to search from, otherwise in some cases you may miss the first result
;
;LookIn - One of the options XlValues, or XlFormulas
;
;Lookat - One of the values xlwhole, or xlPart
;
;Order - xlbyrows, or xlbycolumns
;
;Direction - xlnext or xlprevious
;
;Dtype - Any of the following as text string: Value,Text,Formula,Address,Row,Column
;
;
;=========================================
*/
XL_RangeFindAll(Name,Sht,Range,What, After, LookIn, LookAt, Order, Direction,DType) {
	xla := XLCheck(Name)
	if xla = False
		return
	sht := ((sht="") ?  xla.activesheet.name : sht) 
	
	Rarr := []
	RTS := xla.sheets(Sht).range(Range).Find(What,xla.sheets(Sht).range(After),LookIn,LookAt,Order,direction,1)
	First := RTS.row
	Loop 
	{
		Rarr[A_Index] := RTS[Dtype]
		RTS := xla.sheets(Sht).range(Range).FindNext(RTS)
	}until (RTS.row=First)
	
	return Rarr
} 
/*
;XL_ToObj() Function
;=========================================
;Params:
;		
;
;Name -Specify "" if you want to retrive handle to active excel workbook
;	Otherwise, use the full name of the workbook you want a handle to - e.g. "Book1.xlsx - Excel"
;
;Sht -Specify "" if you want to use the active sheet, otherwise use the Sheet Name e.g. "Sheet1"
;
;Range - Excel Range to convert to an ahk accesible obj i.e. "D10:J100"
;
;DType - Return an object of the cell ranges VALES, or FORMULAS only - Default is Value, specify "Formula" to return formulas
;
;R1C1 - Return object as A1, A2, A3, etc or 1,1; 1,2; 1,3 - Default is Named Columns i.e A1
;
;ByRowsOrCols - Row Indexes, or Column Index (Default is Column)
;
;;=========================================
*/
XL_RangeToObj(Name,Sht,Range,DType="Value",R1C1="",ByRowsOrCols:="")
{
	xlMap:=[]
	
	If Isobject(Name){
		xla := Name
	}else{
		xla := XL_Check()
		if !isobject(xla)
			return
	}
	
	sht := ((sht="") ?  xla.activesheet.name : sht) 
	sArr  := xla.sheets(sht).Range(Range)[Dtype]
	
	Rows:= SArr.MaxIndex(1)		; total rows
	Columns:= SArr.MaxIndex(2)	; total columns
	
	ColStart:=xla.sheets(sht).range(StrSplit(Range,":").1).column	;Start Column Index
	RowStart:=xla.sheets(sht).range(StrSplit(Range,":").1).Row		;Start Row Index
	
	Loop % Rows
	{
		oCurRowNum := (A_Index-1) + RowStart
		CurRowNum := A_Index
		Loop % Columns
		{
			aStart := (a_Index-1) + ColStart
			CurrentCol := (R1C1) ? aStart : ConvertToLetter(aStart)
			If (ByRowsOrCols)
				xlMap[oCurRowNum,CurrentCol]:=SArr[CurRowNum, A_Index]
			Else
				xlMap[CurrentCol,oCurRowNum]:=SArr[CurRowNum, A_Index]
		}
	}
	return xlmap
}
/*
;XL_GetHPageBreaks() Function
;=========================================
;Params:
;		
;
;Name -Specify "" if you want to retrive handle to active excel workbook
;	Otherwise, use the full name of the workbook you want a handle to - e.g. "Book1.xlsx - Excel"
;
;Sht -Specify "" if you want to use the active sheet, otherwise use the Sheet Name e.g. "Sheet1"
;
;Returns object with each key as row of horizontal page break location
;;=========================================
*/
XL_GetHPageBreaks(Name,Sht)
{
	Obj := {}
	
	If Isobject(Name){
		xla := Name
	}else{
		xla := XL_Check()
		if !isobject(xla)
			return
	}
	sht := ((sht="") ?  xla.activesheet.name : sht)
	
	Try
	{
		Speedup(xla,0)
		xla.application.activewindow.view := 2
		pb := xla.sheets(sht).HPageBreaks
		Loop % pb.Count
			Obj[A_Index] := pb.Item(A_Index).Location.Row
		xla.application.activewindow.view := 1
		Speedup(xla,1)
	}
	Catch
	{
		Speedup(xla,1)
		return "Err Getting Horizontal Page Breaks"
	}
	return Obj
}

/*
;XL_GetVPageBreaks() Function
;=========================================
;Params:
;		
;
;Name -Specify "" if you want to retrive handle to active excel workbook
;	Otherwise, use the full name of the workbook you want a handle to - e.g. "Book1.xlsx - Excel"
;
;Sht -Specify "" if you want to use the active sheet, otherwise use the Sheet Name e.g. "Sheet1"
;
;rType - Default returns Column Letter, specify '1' to return column numbers instead
;
;Returns object with each key as Column/Letter of horizontal page break location
;;=========================================
*/
XL_GetVPageBreaks(Name,Sht,rType:="")
{
	Obj := {}
	
	If Isobject(Name){
		xla := Name
	}else{
		xla := XL_Check()
		if !isobject(xla)
			return
	}
	sht := ((sht="") ?  xla.activesheet.name : sht)
	
	Try
	{
		Speedup(xla,0)
		xla.application.activewindow.view := 2
		pb := xla.sheets(sht).VPageBreaks
		Loop % pb.Count
			Obj[A_Index] := (rType) ? pb.Item(A_Index).Location.Column : ConvertToLetter(pb.Item(A_Index).Location.Column)
		xla.application.activewindow.view := 1	
		Speedup(xla,1)
	}Catch{
		Speedup(xla,1)
		return "Err Getting Vertial Page Breaks"	
	}
	return obj
}
habiba zamun
Posts: 1
Joined: 24 Sep 2020, 02:44

Re: Excel Tutorials and XL Function library

25 Sep 2020, 05:43

Hi joe Thank you for your efforts
Vaani_M
Posts: 1
Joined: 01 Oct 2020, 03:10

Re: Excel Tutorials and XL Function library

01 Oct 2020, 03:25

Hey,
Can you explain something on the function and how to use a pivot table. In a coming few days, I will be working on a project [Mod edit: Link removed.] where I have to majorly work on it. Any response will be helpful.
Thanks in advance!
Thelloplz
Posts: 1
Joined: 12 Jan 2021, 12:16

Re: Excel Tutorials and XL Function library

12 Jan 2021, 12:27

Hey Joe, I'm a noobie here. Can you possibly help me?
I have a page on excel that i'm wanting to create a function for that prints to PDF, saves in a certain file and calling upon 2 separate cells and a constant to name the PDF.. the constant is "PO - " and the cells are h2 and f9. h2 is the order name and f9 is the supplier who receives the PO document. If possible, then i would like the function to create a new email inside my Gmail on google chrome and create a new email. Naming the subject what the file is named. The saved PDF file is the exact same as what the subject needs to be in the email.. I tried watching and changing the format of #43 you posted a couple years back and reformatting it but had no luck. I am a noob when it comes to AHK so any advice on how to learn this will be greatly appreciated.
fubarsanfu
Posts: 2
Joined: 18 Jan 2021, 12:01

Re: Excel Tutorials and XL Function library

18 Jan 2021, 12:48

Thelloplz wrote:
12 Jan 2021, 12:27
Hey Joe, I'm a noobie here. Can you possibly help me?
I have a page on excel that i'm wanting to create a function for that prints to PDF, saves in a certain file and calling upon 2 separate cells and a constant to name the PDF.. the constant is "PO - " and the cells are h2 and f9. h2 is the order name and f9 is the supplier who receives the PO document. If possible, then i would like the function to create a new email inside my Gmail on google chrome and create a new email. Naming the subject what the file is named. The saved PDF file is the exact same as what the subject needs to be in the email.. I tried watching and changing the format of #43 you posted a couple years back and reformatting it but had no luck. I am a noob when it comes to AHK so any advice on how to learn this will be greatly appreciated.
I have answered most of this on Reddit - https://www.reddit.com/r/AutoHotkey/comments/kvv6mr/amateur_that_needs_help_excel_print_to_pdf/gj419wa/. You can then use the same variables to create subject etc in gmail.
pallabibaruah18
Posts: 14
Joined: 20 Jan 2021, 23:20

Re: Excel Tutorials and XL Function library

01 Feb 2021, 11:34

Ohh this is a really good thread. Thank you for posting.
Dweezil
Posts: 1
Joined: 22 Apr 2021, 14:08

AhK to Open & Refresh an Excel Power Query Table

22 Apr 2021, 14:29

Hi Joe,

I've spent so much time on this. To summarize, I have an Excel report that is emailed to me every day & I want to post its contents automatically to a SharePoint list that updates a dashboard. However the final piece of the puzzle is in the middle, needing assistance refreshing the Excel Power Query Table (not a pivot table) & closing that file.

Remembering the long lost days of using AutoHotKey's ScriptWriter years ago, I have no doubt that AhK can open the file and just refresh the table. So far I can get my script to open the file, but I can't get it to refresh the table & then close the file.

My script which opens the file:

^p::
Run, C:\Users\MyName\OneDrive - CWOPA\Lean\COVID19 Daily Updates\OMAP\omap power query.xlsx

And a pic of the power query table that needs to be refreshed:
omap.jpg
Excel file w/power query table to refresh
omap.jpg (74.15 KiB) Viewed 25270 times
Thank you for any guidance that you can provide!!

Return to “Tutorials (v1)”

Who is online

Users browsing this forum: No registered users and 33 guests