Power Query and Open Excel Files

Old school solution

Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.

Back in the old days Excel had an option that allowed you to always create a backup of a file. This meant that when you saved the file Excel would take a copy of the current file as a backup and then save the current file. This meant you always had two versions of the file available – current and previous. This feature can be used to capture a copy of the current, open file. If you click Save twice then both files will have exactly the same content.

Let’s say you are using Excel files to capture live data. The files you want to extract data from are most likely open and being used. If a file is open Power Query will not be able to read from it. To get around this issue you can turn on the backup feature to create a copy of the file. The backup file is always closed.

Instead of importing the live Excel files you would import the backup versions which should be closed. In Excel the backup version and will have the same name but a suffix of .xlk. When importing the files In Power Query from a folder you will need to make sure you filter by the backup suffix so that you only import the .xlk files.

Backup

To turn on the backup feature you need to go through the Save As option. You can access Save As by pressing the F12 function key.

The backup needs to be set up on each of the files that you want to work with.

At the bottom of the Save As dialog there is a Tools drop down.

Click General Options.

Tick the Always create backup option, click OK and then click Save.

You will need to overwrite the current file, even if you haven’t changed the name.

The backup is always created in the same folder as the file with the same name with a different suffix .xlk.

Please note: I reserve the right to delete comments that are offensive or off-topic.