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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     amp  vbNewLine  amp  cn Name         Result   MsgBox strMsg  vbYesNo           If Result   vbYes Then             cn Delete         End If      Next cn  End Function

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-2010