CODEWITHSUNDEEP
excelexcel-2010
dartacus
dartacus

Reputation: 644

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?

Upvotes: 11

Views: 114628

Answers (11)

Uttam
Uttam

Reputation: 736

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.

Upvotes: 1

Amy
Amy

Reputation: 177

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

Upvotes: 1

Richard Ho
Richard Ho

Reputation: 11

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

Upvotes: 1

Dang Thanh
Dang Thanh

Reputation: 11

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

Upvotes: 1

Chuck
Chuck

Reputation: 41

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

Upvotes: 4

David Parsons
David Parsons

Reputation: 31

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.

Upvotes: 3

user3702189
user3702189

Reputation: 21

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...

Upvotes: 2

user3650998
user3650998

Reputation:

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.

Upvotes: 9

Guest
Guest

Reputation: 11

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

Upvotes: 1

dhacohen
dhacohen

Reputation: 47

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

Upvotes: 3

Dave
Dave

Reputation: 94

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.

Upvotes: 7

Related Questions