Looks like a silly question, but Excel 365 defaults to creating a new query type connection when you want to "get data". We are used to creating OLEDB connections to Oracle, SQL server and MS Access databases at work and in previous version of Excel, this was alwas a data connection. Excel 365, defaults to creating a new style Query (Data tab, Get data -> From Other Sources -> From OLEDB). This new query style does not suit our needs. I haven't found any way to create the old style dataconnection in Excel 365. Can anyone give me pointers how to do this?
Background:
We are sending Excel files with data to customers and we change connection strings and queries using VB Script so as not to send out too much internal information (tables, connection details, credentials). So we update the dataconnection from a VBScript file program where we set connectionstring and sometimes the commandtext.
Differences I see:
In the Query, the connection string is something like "OLEDB:Provider=Microsoft.Mashup.Oledb.1;Data Source = $Workbook$;Location=CURRENT_PERIOD;Extended Properties=""
The commandtext is "select * from [Query1]"
Changing the commandtext here results in an error "[Expression.Error] The import consumables matches no exports. Did you miss a module reference?"
In the older connection style, de connection string is "OLEDB;Provider=MSDASQL.1;DSN=" with commandtext "select * from current_period". We could change the commandtext at will to get different results. We could also change the connection string to include login credentials so the query would run.
Also by changing the connection string you could change the connection to OLEDB connection, ODBC connection or a connection to an MS Access database. Changing the connection string in the new query type connection results in an error and does nog give a different type of connection.
It is just not working for what we do and I can't find a way to create the old style connection.
You need to enable the legacy import wizards. Go to File > Options > Data. Then check all of the legacy import wizards.
Once these settings have been saved, you will see these options appear under the Data > Get Data menu.