Excel Tutorials and XL Function library

Helpful script writing tricks and HowTo's
User avatar
Joe Glines
Posts: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Excel Tutorials and XL Function library

Post by Joe Glines » 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.

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

burque505
Posts: 1358
Joined: 22 Jan 2017, 19:37

Re: Excel Tutorials and XL Function library

Post by burque505 » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Sdamico
Posts: 1
Joined: 28 Jun 2019, 07:55

Re: Excel Tutorials and XL Function library

Post by Sdamico » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

JKnight_xbt33
Posts: 99
Joined: 18 Sep 2019, 02:06

Re: Excel Tutorials and XL Function library

Post by JKnight_xbt33 » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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.

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Fleurpink
Posts: 1
Joined: 20 Jan 2020, 06:03

Re: Excel Tutorials and XL Function library

Post by Fleurpink » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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/

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Axxi
Posts: 15
Joined: 02 Apr 2020, 10:56

Re: Excel Tutorials and XL Function library

Post by Axxi » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

nik2502
Posts: 3
Joined: 15 Jun 2020, 09:00

Re: Excel Tutorials and XL Function library

Post by nik2502 » 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: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel Tutorials and XL Function library

Post by Joe Glines » 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

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Tre4shunter
Posts: 72
Joined: 26 Jan 2016, 16:05

Re: Excel Tutorials and XL Function library

Post by Tre4shunter » 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: Yesterday, 02:44

Re: Excel Tutorials and XL Function library

Post by habiba zamun » Today, 05:43

Hi joe Thank you for your efforts

Post Reply

Return to “Tutorials”