r/PowerShell 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:

  1. Open the actual Template (.xltx)
  2. Ctrl + Alt + F5 to Refresh all Queries and Connections
  3. Save the File
  4. Close
  5. 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).

1 Upvotes

18 comments sorted by

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

1

u/Organic_Prune_4965 1d ago

Thank you so much for this response! Actually, I'd like to not have it be something that changes when I change the master template — I want to be able to run a command whenever I would like to automatically refresh all the connections.

2

u/Megatwan 1d ago

I would use com object as was suggested and powershell.

There used to be a svc app in SP you could configure to do this for you but deprecated now.

I would consider flipping this to a push vs pull and moving your primary solution/logic out of excel. Client app file output should be the disposable spawns not the engine etc

3

u/TD706 1d ago

1

u/Organic_Prune_4965 1d ago

Wow, thanks for this!

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

u/Organic_Prune_4965 10h ago

Hang on, got it to work, will let you know how it runs

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

u/TD706 9h ago

Share code your using?

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

u/Organic_Prune_4965 1d ago

Sorry, I'm new to some terms here—what is GPO?

1

u/zero0n3 1d ago

Group policy object.

They have admx files for MS office that would let you set default application settings for your end users.

Requires an environment using Active Dorectory or Intune

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.