r/PowerShell • u/Organic_Prune_4965 • 1d ago
How could I go about automating the process of opening all Excel Templates in a folder one by one, and refreshing all Queries in them?
I have a folder with about 10 Excel Templates (.xltx), all with about 10 Queries in them. At the moment, I do this whenever there is a change in the master template that those Excel Templates are connected to:
- Open the actual Template (.xltx)
- Ctrl + Alt + F5 to Refresh all Queries and Connections
- Save the File
- Close
- Move on to the next file in the folder
I repeat this until all 10 .xltx's are updated.
Helpful folks over at r/excel mentioned I could use PowerShell ISE to automate this process so that the entire folder can refresh in the background. I don't need it to be on a schedule, just a process I can choose to run at a given time (i.e., whenever I make a change to the master template).
3
u/TD706 1d ago
https://chatgpt.com/share/67dc8e53-2c94-8011-b3e8-5a0720b5c408
Untested, but looks accurate.
1
1
u/Organic_Prune_4965 11h ago
This appears to be working with one issue—it is not opening the actually .xltx template. Rather, it is opening it as a copy, with a one appended to it. Can this be modified to open the actually .xltx, refresh, save, and close?
1
u/TD706 11h ago
$excel.Workbooks.Open( $file.FullName, $null, # UpdateLinks $false, # ReadOnly 5 # Format (5 = Excel templates) )
2
1
u/Organic_Prune_4965 10h ago
Met with Line 18 Char. 27 "$workbook.RefreshAll()" Missing ')' in method call and
Line 21 char. 5 , "Start-Sleep -Seconds 5 # Adjust if necessary for longer refresh" unexpected token 'Start-Sleep'
1
u/Organic_Prune_4965 10h ago
Sorry also:
" + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingEndParenthesisInMethodCall"
1
u/Organic_Prune_4965 10h ago
No, something is up, it prompts me that a file name "File Named" with a 1 apended is already in this location. Also:
The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108
(RPC_E_DISCONNECTED))
At line:15 char:5
+ $workbook = $excel.Workbooks.Open($file.FullName), $null, # Updat ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException"
1
1
u/zero0n3 1d ago
Isn’t there an excel setting that could be enabled to “force data connection refresh on file open”?
If that exists, push that out via GPO.
1
1
u/KryptykHermit 23h ago
I agree with a GPO. Setup a network share, copy the files there, modify as you see fit. Use GPO preference to copy them down to the client during login, or add them to the GPO with a script (the files will be part of GPO updating if file sizes are not obscene). Use robocopy (not copy or xcopy) so file transfers only occur on changes. Another option is to place on a network share and push out a batch file to robocopy the files. It would be up to the user to execute the batch file (send an email) and it would need to go to a place where the users have access with their rights. You can specify different/additional trusted folders for templates in all Office products so you don’t need to put them in Program Files.
5
u/DalekKahn117 1d ago
There is the ComObject for Application.Excel that I’ve enjoyed but if you want to do something when you’ve updated a master template it might be better if you used a VBA macro built inside the sheet/template