- 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)
Excel Tutorials and XL Function library
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Excel Tutorials and XL Function library
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!
Last edited by Joe Glines on 29 Nov 2020, 08:42, edited 4 times in total.
Sign-up for the HK Newsletter
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Re: Excel Tutorials and XL Function library
@Joe Glines, thanks for putting links to all your excellent materials together in one spot.
Regards,
burque505
Regards,
burque505
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
@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
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Re: Excel Tutorials and XL Function library
This is so helpful!
Do you also have a tutorial on running pivot tables via autohotkey?
Thanks
Do you also have a tutorial on running pivot tables via autohotkey?
Thanks
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
@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
You can check it out here
Sign-up for the HK Newsletter
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
-
- Posts: 135
- Joined: 18 Sep 2019, 02:06
Re: Excel Tutorials and XL Function library
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.
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.
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
You'd need to actually tell me what you tried to run...JKnight_xbt33 wrote: ↑25 Dec 2019, 09:22Thanks 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.
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
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Re: Excel Tutorials and XL Function library
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.
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
Fleurpink wrote: ↑21 Jan 2020, 15:04This 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
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Re: Excel Tutorials and XL Function library
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!
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!
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
@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
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Re: Excel Tutorials and XL Function library
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.
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.
- Joe Glines
- Posts: 771
- Joined: 30 Sep 2013, 20:49
- Location: Dallas
- Contact:
Re: Excel Tutorials and XL Function library
@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
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
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey
YouTube
Quick Access Popup, the powerful Windows folders, apps and documents launcher!
-
- Posts: 139
- Joined: 26 Jan 2016, 16:05
Re: Excel Tutorials and XL Function library
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.
Functions:
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."
}
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
}
-
- Posts: 1
- Joined: 24 Sep 2020, 02:44
Re: Excel Tutorials and XL Function library
Hi joe Thank you for your efforts
Re: Excel Tutorials and XL Function library
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!
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!
Re: Excel Tutorials and XL Function library
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 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.
-
- Posts: 2
- Joined: 18 Jan 2021, 12:01
Re: Excel Tutorials and XL Function library
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.Thelloplz wrote: ↑12 Jan 2021, 12:27Hey 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.
-
- Posts: 14
- Joined: 20 Jan 2021, 23:20
Re: Excel Tutorials and XL Function library
Ohh this is a really good thread. Thank you for posting.
AhK to Open & Refresh an Excel Power Query Table
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:
Thank you for any guidance that you can provide!!
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:
Thank you for any guidance that you can provide!!
Who is online
Users browsing this forum: No registered users and 16 guests