Remove a data connection from an Excel 2010 spreadsheet in compatibility mode

Question

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 1

The data imported through external connections is imported into a table. To unlink the table from data, please:

1. Click on any cell in the table with imported data.
2. Click "Table Design" on menu.
3. In the toolbar group "External Table Data", click "Unlink". Click OK on the confirmation prompt.

This should unlink the table and remove any connection related information from the Excel file.

In VBA, you can use the below command:

<sheetname>.ListObjects("<table name>").Unlink

please replace <sheetname> with the name of the sheet and <tabe name> with the name of the table that is linked to external data.

Answer 2

I had the same problem today. If after you delete all of the connections, the connection properties still live on. I clicked on properties, deleted the name by selecting the name window and deleting it.

A warning came up to verify I really wanted to do it. After selecting yes, it got rid of the connection. Save the workbook.

I am a hack at Excel but this seemed to work.