For this post, I am going to start from the beginning to give an overview of what I am trying to accomplish. I work for a company where I have built a PO calculator that operates from automatic price updating. There is an excel file for the Pricebook (automatically updated and exported from another program), which goes to another file that is a Part # builder, then to the PO Calculator (Pricebook.xlsx -> Part#Builder.xlsx -> POCalculator.xlsx). This is the flow of formula references in excel (lots of vlookup).

Unfortunately with the way excel updates its formulas, these books do not automatically update their cells unless they are open(ed). I wrote a very basic PowerShell script to open these workbooks and close them every night, which updates the formulas. This works great for me, but other people need to use the PO Calculator file without having access to the Part # Builder and Pricebook.

I shared a folder with the PO Calculator file and a How-To Word Doc with the people that need it so they can use it. For them to have access to the automatic price updating, they either need access to the “Part#Builder.xlsx” file or have to copy the “POCalculator.xlsx” file from the shared OneDrive folder every day. The people I work with aren’t the greatest with computers, so this process will likely result in a plethora of copied files with the same name that will eventually confuse them, possibly causing them to use files that are not updated with the most recent pricing.

Instead of having the other people constantly download a copy of the PO Calculator for each PO, I thought it would be easy to just create a template excel file of the PO Calculator (.xltx). The issue is now the template will not automatically update using the opening and closing method I developed in PowerShell.

My next idea was to automate the deletion and creation of a new template from the POCalculator.xlsx every night when it updates. Unfortunately, I have been unable to find anything about how to automate exporting excel workbooks to excel templates.

To summarize, I have a POCalculator.xlsx file in a folder in OneDrive that I need copied to another folder in OneDrive as a .xltx file every night.