Excel Tutorials and XL Function library

Helpful script writing tricks and HowTo's
User avatar
Joe Glines
Posts: 646
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

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.
burque505
Posts: 1074
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: 646
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

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
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: 646
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

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

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 11 guests