Remove a data connection from an Excel 2010 spreadsheet in compatibility mode
Situation: an Excel 2010 workbook is created using data from oracle. A data connection is created to achieve this. The excel workbook is then saved as a .xls file so that customers with older versions of office can access it.
Problem: a warning message is displayed when the workbook is opened:
'Security warning Data Connections have been disabled [ Enable content]'.
We think this is shown because the data connection is still present in the workbook. You can go to Data->Connections and remove it, but when the workbook is saved and re-opened, the data connection has come back (although with empty definitions fields).
Things tried: removing all data connections using a macro: no joy. The 'Name Manager' method described here (http://www.mrexcel.com/forum/showthread.php?t=467938) : no joy.
Has anyone else seen this behaviour?
Answer
Select a cell in the cell range in which the data is imported, then Menu > Data > Properties > uncheck save query definition.
Properties will be greyed out unless a cell in the data import range is selected.
You can find out the range in which the data isimported by:
Menu > Data > Connections > (select connection) > Click here to see where the selected connections are used.