7

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?

radiocontrolled
  • 517
  • 2
  • 9
Habib Inman
  • 151
  • 1
  • 1
  • 7
  • Why down vote me? Add some insight as I know if something missed. – Habib Inman May 12 '16 at 12:20
  • 1
    As a secondary note, I am not the down voter, however for general Excel help I would steer away from SO as if you read stackoverflow.com/tour it states: "Stack Overflow is a question and answer site for professional and enthusiast programmers..." – Bob Goblin May 12 '16 at 13:50
  • 1
    Though it could be worded better, it appears to be a professional development question, not general Excel how-to. Power Query has some prompts that are a bit tricky to get around but developers need to understand so solutions can be distributed to users. – Ben Jun 13 '18 at 14:20

3 Answers3

10

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

Bob Goblin
  • 1,251
  • 3
  • 16
  • 33
10

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
Cody Mayers
  • 355
  • 6
  • 19
2

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

brunoazev
  • 96
  • 6