다음을 통해 공유

Use VBA to enable Fast Combine in Excel 2016

  • 아티클

As some of you probably know, in Excel 2016 we have added new Object Model interfaces to light up automation scenarios with queries (aka Power Query). The new interfaces are described here and you can download a workbook sample that demonstrates all load flows here.

Today I am happy to share with you a new interface that is quite useful for refresh scenarios:

ThisWorkbook.Queries.FastCombine = True

A short background before we continue -

Power Query technology allows you to combine data from different sources. On some scenarios, Power Query engine can combine the data faster by taking part of the information from the first data source, and send it to the second source to retrieve only the necessary data.

Whenever you build queries that combines data from multiple data sources, you are asked to define the privacy level for each data source.

Once you select the appropriate privacy level, and if the two data sources are both Public or Organizational, then Power Query engine can use data from the first data source to speed up the load from the second data source. You should note that there are many cases where speeding up the load is not relevant - For example, if you join between two text files.

And here is where Fast Combine gets the spotlight. According to the official support article (read more here):

"Fast Combine is a workbook setting that is set to Combine data according to your Privacy Level settings for each source by default, which means that Fast Combine is not enabled.

Enabling Fast Combine, will ignore the privacy levels and potentially improve performance (You can reach Fast Combine in Excel 2016 by clicking New Query --> Query Options, and selecting Privacy in the Query Options dialog)."

And here comes an interesting fiction story (though I am sure that some elements are based on real events that might happened to some of you - For example: here is a relevant thread on Power Query TechNet forum)

Let's assume I am an author of a very important workbook in my organization. I have built a workbook that uses Power Query to combine data from multiple data sources. During the creation of the workbook, I have stumbled upon the Privacy Levels dialog, but managed to select the correct levels.

One day, the reputation of my workbook reaches the ears of my CEO, and I am asked to share the workbook. Excited with my good new fortune, I send the workbook to my CEO. A hour later I gets back his response:

"What are those Privacy Levels? Should I be concerned?"

"Don't worry", I reply after finding out that I can enable Fast Combine to bypass the Privacy Level prompt, "Please use this workbook, you will not be asked to use privacy levels any longer".

5 minutes later, I discover that the Fast Combine setting will not impact other users. But it is too late... I get different emails now. This time from my CIO, CISO and CFO.

And here is a more common scenario -

You created an automation that refreshes your queries (Here is a very useful blogpost by Ken Puls how to do it). When the automation runs on your computer, it "remember" the privacy levels, or the Fast Combine settings and perform the refresh, but if you want to run the automation on another user's computer, how can you avoid the Privacy Levels prompt? If you want to learn more about this scenario, read Ken Puls blogpost here.

The Solution in Excel 2016

If you use the latest version of Excel 2016, you can now enable Fast Combine through VBA and propagate it to other users:

ThisWorkbook.Queries.FastCombine = True

There are cases where you can combine it with:

Application.DisplayAlerts = False

For example, here is a macro that performs silent Refresh All and bypasses Privacy Levels prompts:

Private Sub SilentRefresh()

Application.DisplayAlerts = False

ThisWorkbook.Queries.FastCombine = True

ActiveWorkbook.RefreshAll

End Sub

If you have a workbook that is refreshed when the file is opened, you can add this macro and share it with your consumers. This will bypass the Privacy Levels prompt for any user who opens the workbook.

Private Sub Workbook_Open()

    ThisWorkbook.Queries.FastCombine = True

End Sub

Here is a workbook that demonstrates how a silent refresh can be done when the workbook is opened. When you open it you will notice that the Privacy Levels prompt is not shown, but if you remove the macro, you will be required to set Privacy Levels.

Caution    You should avoid using this Macro with highly sensitive or confidential data, and instead keep using the Privacy Levels dialog to configure such data sources as Private. You should also avoid running Macros on workbooks from untrusted source.

Fast Combine On.xlsm

Comments

  • Anonymous
    December 25, 2015
    Could you please suggest a workaround for Excel 2013/ 2010
  • Anonymous
    December 25, 2015
    Sorry Sam,
    There is no workaround on these versions. I recommend you to send a frown, share your scenario on TechNet forum or submit a request on uservoice
  • Anonymous
    July 14, 2016
    How can you do this via com in C#?