MS Query is available but not working in Excel after a recent update to office

Question

I have updated my options by going to File/Options/Data and marking the flag "from Microsoft Query Legacy". However when I select the legacy options under Data/Get Data/Legacy Wizards, MS Query is in the list however it's grayed out. When I select the grayed out option, Excel goes into a wait state and nothing happens. I cannot figure out how to have the option enabled so it works.

I also don't have the option for "from Microsoft Query" in my "all commands" when trying to customize the ribbon.

As a alternative attempt at solving the issue, I completely reinstalled office, uninstalled using the command prompt application SaRACmd, deleting the cache folders, deleting registry keys, etc. I attempted to completely remove office from my computer. I reinstalled and have the same issue as described above.

So the issue is how do I get the MS Query app enabled again? I did find the executable for MS query and can run a query standalone but cannot send the results directly to Excel. Please advise. Thanks.

Answer 1

This is an Excel issue more than a MSQuery problem because you can still run MSQuery outside of Excel, which has become our workaround. It's located at "C:\Program Files\Microsoft Office\root\Office16\MSQRY32.EXE". If you run this you can run your queries and save them on your drive somewhere then double-click on the "Yoursavedquery.dqy" file and it will open Excel with the data from your query. Even if your MSQuery locks up in Excel you can still run MSQRY32.EXE.

Answer 2

Correct MS Query works fine standalone . I have done a lot of testing and also reverted back versions of Office to before the legay wizard change was put in . What I can tell you is the problem was still happening in the old versions of Excel . What had not been reverted back was One Drive and this is where the issue is related to , so it is Excel / MS Query issue with Onedrive/Sharepoint .

Answer 3

If you use the MSQRY32.EXE file it will run MSQuery then do your regular queries and when it pulls up the data in the query window click on file save and save the file on your hard drive or your desktop, it will create a file like Query.dqy, you can then double-click on that file and it will open in Excel.

You might also check using task manager while your computer is locked up after trying to run msquery and look for Microsoft Sharepoint, if you see that task end the task and you will see msquery come up and you are back in business.

I created a short batch file that I can click on to kill the task. This is in the .bat file "taskkill /F /IM microsoft.sharepoint.exe" open notepad enter that line without the quotes and save as Fixmsquery.bat or something like that. Make sure when saving you click on all files so it doesn't put a .txt extension on the end,