Always Allow Native DB Queries

问题:

In Excel 2013 how can I change options (or is it possible) to always allow native DB Queries?

Can someone walk me through how to set this up?

回答1:

When in Excel
1) Click Power Query from the "Ribbon" across the top
2) Choose Options
3) From the Options Window that loads select Security from the left
4) Untick the box that says "Require user approval for new native database queries"
5) Click OK

回答2:

I don't yet have a high enough reputation to comment this on user2676140's answer, but to piggyback off of it in case anyone comes to this thread for Excel 2016 instead, this option is in:

1. "Data" Ribbon
2. "Get & Transform" Group
3. "New Query" drop-down
4. "Query Options"
5. "Security" section
6. Untick the box that says "Require user approval for new native database queries" and click OK

If I find a VBA solution I'll post it as well.

Edit: To bypass the alert in VBA, although technically it isn't unticking the box for you, use the following:

Application.DisplayAlerts = False
'Run your code to execute the query
Application.DisplayAlerts = True

回答3:

You can also update the Windows registry to achieve this behavior, as suggested by one of the posts in this forum topic.

Creating a DWORD value with the name DisableNativeDbQueryPrompt and setting the value to 1 will disable native query prompts.

The registry path for the PQ add-in:

32-bit (on a 64-bit system): HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft Power Query for Excel

64-bit (or 32-bit on a 32-bit system): HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel

For Office 2016:

32-bit (on a 64-bit system): HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\PowerQuery

64-bit (or 32-bit on a 32-bit system): HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\PowerQuery