Excel Tutorials and XL Function library
Posted: 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!
- Create / Connect to Excel with AutoHotkey via COM
- AutoHotkey and Excel Object model: Application, Workbook, Worksheet
- Shading cells with AutoHotkey via COM
- Obtain First row, Last row, # Used rows
- Detecting Used Range (first column, last column, first row, last row, etc.)
- Obtain First / Last Column & numeric to string converter
- Setting Horizontal and Vertical cell alignment
- Simplify writing AutoHotkey code using the Macro recorder & Excel constants
- Manipulate columns (Insert, Delete, set width)
- Insert, Delete, set Height of Rows
- Freeze Panes, Toggle screen updating, Inject file name into MRU
- Merging, Shrinking, and Wrapping cells
- Set cell Number format
- Font size, type, bold, italic, underline
- Adding / Removing / Changing Borders in Excel
- Creating “pretty link” for email merge from data in cells
- Using Offset and a range to iterate over cells and create a “better” email merge
- Finding & Returning specific header locations (for use in Filtering, Sorting, Merging, etc.)
- Clear Excel: Content, format, Data, Notes, comments, etc.
- Leverage built-in Excel worksheet functions & pass a method as a parameter (simplify code maintenance)
- Renaming, Moving, Activating, and Changing the color of Worksheets / Tabs
- Delete an entire Column or Row based on a Value
- Easily Loop over Cells in Excel by using a For Loop in AutoHotkey. I also demonstrate how to detect selection area
- Copy a range to the clipboard or a variable from Excel
- Various ways to Paste in Excel (Think of Paste Special but progromatically doing it)
- Selecting a Cell / Range of Cells
- Inserting and Deleting Comments / Notes
- Inserting and Deleting Worksheets with Excel & AutoHotkey
- Inserting and Deleting Hyperlinks
- Creating personalized emails. Also see our Outlook webinar where we use Excel with Outlook
- Search & Replace values
- Multiple Search / Replace values. Also delete #Null!
- Find value and return location (Also allows for finding the nth instance of it)
- Find multiple text values in Header row and return their locations
- Deleting blank columns and/or Rows
- Sorting Columns in Excel
- Sorting Rows
- Remove Duplicate Rows
- Setting and Filtering data in Excel
- Get and Set value on a specific worksheet
- Connect to or Launch Excel. Also how to change it’s visibility and Close workbook and Quit Excel
- Opening various file types (XLS, XML, CSV, TSV/TXT, XLSX, and HTML) in Excel
- Saving Excel files in various file types (CSV, Tab Delimited, XLSX)