How to Bulk Refresh PowerPivot Workbooks on your PC

There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are:

1. Using Power BI
2. Using SharePoint Auto Refresh
3. Using some sort of custom built desktop automation tool

The problem with Power BI and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap. I cover the downsides of Power BI in this post about how to access PowerPivot on iPad. SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that brings me to option 3 – desktop automation.

How to bulk refresh powerpivot workbooks on your PC

I have been a closet VBA coder for many years. In fact I first taught myself “really bad coding” using Lotus 123 Macros. Over the years I have accumulated code that I reuse for new purposes. At some stage about 10 years ago, I created a VBA workbook that will list the contents of a folder into a workbook. As I was thinking about the need to refresh PowerPivot Workbooks on a PC, I realised that I could reuse this code as the core of the project. Now I don’t profess this to be the best code, or even good code for that matter. But I can tell you this has been working successfully for me for some time.

How the refresh process works

The current version of my workbook is semi-automatic – that is to say that it needs human intervention to kick off the process. I have not had a need for a fully automated solution in the past so I have not gone to the next step of completing the full automation. I may get back to this some time, or build it for a specific customer request if needed.
So the process as it currently stands is to…

1. Open the refresh workbook.
2. Select which folders you want to refresh
3. Press the “refresh” button

The tool then systematically works through each file in the selected folder(s) and opens, refreshes, saves and closes the file. At the end of the process, you get a report of which workbooks were refreshed and how long each one took to complete.

How to prepare to use the tool

The first thing to do is create a clean folder somewhere. That folder can be a folder on your PC that is mapped to SharePoint, or it can be a folder on a shared network drive, or simply a folder on the local PC containing the VBA workbook. Then create sub folders for every class of document you need to refresh on a different cycle. I have used time periods, but you can use anything that makes sense to your needs.

Then place your PowerPivot workbooks in the appropriate sub folders.

Open the refresh tool

You can download a copy of the report refresh tool here. Open the Refresh Tool Workbook and update the setup data on the right hand side so that it points to your main folder. You can also specify a filename wildcard descriptor if needed.

Note the full path of the refresh folder in cell J1 and the file pattern in cell J2.

The next thing to do is click on the button “Update File List”. You will see the table on the left hand side refreshes with the sub folders and file names (see example below).

Now you need to manually add the names of each of your sub folders from the table on the left into the table on the right – place the folder names in the column “Folders to Refresh”. This is a “once off” setup task. Once you have done this you are all set to rock and roll.

How to operate the tool

The process for refreshing the workbooks is now simple.

1. Open the auto refresh workbook.
2. You can manually refresh the file list if you want to see any new files you have placed in the sub folders – just click the “update file list” button. Note that this is not required for the full process to work, this is just for your own information if you want to see an updated list without first running the refresh.
3. In the table on the right hand side, place an ‘x’ against the folders you want to refresh
4. Click on the button ‘Refresh Selected Reports’ and go and get a coffee.

I hope you find this tool useful. Let me know how it works for you.