[excel] 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?

I had the same problem. Please try this:

Sub deleteConn(xlBook)
    For Each Cn In xlBook.Connections
        Cn.Delete
    Next Cn

    For Each xlsheet In xlBook.Worksheets
        For Each Qt In xlsheet.QueryTables
            Qt.Delete
        Next Qt
    Next xlsheet

End Sub

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.

I manage to solve this in excel 97-2003, in a file with .xls extension this way: I went to the page where I had the linked data, with the cursor over the imported data table, go to tab Design --> External Data Table --> Unlink Unlink all tables (conections), delete all conections in Data --> Conections --> Conections save your work and done! regards, Dan

That is okay for removing of data connections by using VBA as follows:

Sub deleteConn()
    Dim xlBook As Workbook
    Dim Cn As WorkbookConnection
    Dim xlSheet As Worksheet
    Dim Qt As QueryTable
    Set xlBook = ActiveWorkbook
    For Each Cn In xlBook.Connections
        Debug.Print VarType(Cn)
        Cn.Delete
    Next Cn
    For Each xlSheet In xlBook.Worksheets
        For Each Qt In xlSheet.QueryTables
            Debug.Print Qt.Name
            Qt.Delete
        Next Qt
    Next xlSheet
End Sub

I had many connections I couldn't delete but didn't want to delete them all. So, I extended Dang Thanh and Richard Ho 's solution so it would prompt user to decide which connections to delete.

To delete some but not all connections in a Workbook:

Public Function deleteConnections()

    Dim xlBook As Workbook
    Dim cn As WorkbookConnection
    Dim Result As Variant

    Set xlBook = ActiveWorkbook

    For Each cn In xlBook.Connections

        Dim strMsg As String
        strMsg = "Would you like to delete: " & vbNewLine & cn.Name
        Result = MsgBox(strMsg, vbYesNo)

        If Result = vbYes Then
            cn.Delete
        End If

    Next cn

End Function

When the Import Data box pops up click on Properties and remove the Check Mark next to Save query definition When the Import Data box comes back and your location is where you want it to be (Ex: =$I$4) click on OK and your problem will be resolved

I had the same problem. Get the warning. Went to Data connections and deleted connection. Save, close reopen. Still get the warning. I use a xp/vista menu plugin for classic menus. I found under data, get external data, properties, uncheck the save query definition. Save close and reopen. That seemed to get rid of the warning. Just removing the connection does not work. You have to get rid of the query.

I have experienced that a drop-down menu, referring to a control range (for example after copying sheets from one workbook to another), will keep that cell reference after copying the worksheet, and keeps a data connection which is invisible in "Connections". I found this in the "Search" menu in the ribbon, where an arrow can be selected to mark objects. Underneath the arrow is a menu selection to see all the objects listed in a panel. Then you can delete those unwanted objects and the data source/connection is gone...

Excel 2010: On the File/Info page, go to 'Related Documents' and click break links => warning will appear that all linked values will be converted to their data values => click ok => done