Search code examples
excelodbcoffice365powerqueryms-query

Locate and/or reinstall link to MS Query in Excel 365

I have a small data querying and analysis team that uses Microsoft Query to pull data from an ODBC connection. We are running Microsoft 365 and tapping into data sources inside Excel. The ODBC data source is 32-bit, so our install is 32-bit. Primarily, we update and refresh existing sources, but we noticed in the last week that the access to Microsoft Query from the menu seems to be missing (seems removed).

Here's the investigation we've done so far.

  1. Using what seems to be the current article for using MS Query, we are trying to use the menu option of Get Data > From Other Sources > Microsoft Query. The Microsoft Query option is no longer there.

Link: https://support.microsoft.com/en-us/office/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

  1. Excel Options > Customize Ribbon > All Commands > From Microsoft Query isn't available.
  2. Open Excel in safe mode.
  3. Review Excel add-ins, looking for disabled add-ins.

I have also scrubbed Office and installed Office 2016 and Office 365 fresh (not at the same time). In these tests, the options are still in place in Office 2016 (slightly different menu path -> Data tab > From Other Sources > From MS Query) but not in Office 365.

I realize I can still connect to the ODBC data source (Sybase ODBC driver) using PowerQuery, but Microsoft Query has been a useful tool to initially design queries and get raw data, then transform it in PowerQuery.

Any comments, knowledge, or fixes are welcome.

Solution

  • In Excel 365, select File >> Options >> Data. Then in the "Show legacy data import wizards" section, check the box to show "From Microsoft Query (Legacy)".

    Microsoft query will now show in the ribbon as Data >> Get Data >> Legacy Wizards >> From Microsoft query (Legacy).