none
VBA to remove all data connections (Excel 2013)

    Question

  • I have created a macro that removes all the data connections from a workbook.  When I run it all the connections are removed but then I get this error:

    Run-time error '5':

    Invalid procedure call or argument

    The code is:

    Sub RemoveConnections()
    For i = 1 To ActiveWorkbook.Connections.Count
    If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
    ActiveWorkbook.Connections.Item(i).Delete
    i = i - 1
    Next i
    End Sub

    Can somebody help me out?  Not sure why it's erroring out instead of just ending once all the connections are removed.



    • Edited by nmuleski1 Tuesday, February 4, 2014 11:03 PM clarification
    Tuesday, February 4, 2014 5:02 PM

Answers

  • Try this:
    Sub removeconnections()
    Dim xConnect As Object
    For Each xConnect In ActiveWorkbook.Connections
    If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
    Next xConnect
    End Sub
    

    • Marked as answer by nmuleski1 Tuesday, May 13, 2014 2:00 PM
    Monday, February 10, 2014 8:17 AM
    Moderator
  • I've tested this code in excel 2013,it works fine.
    I get this error message only when I add a data Model when creating pivot table. It is a new in-memory analytics engine within excel 2013.

    Try to delete the related pivot table and then reopen your excel file to run this code.
    Also please check if tick the option 'prefer the Excel Data Model......'
    File->Options->Advanced->Data, prefer the excel data model... untick the option.


    • Edited by zkandyModerator Friday, February 7, 2014 3:50 AM
    • Marked as answer by nmuleski1 Monday, February 10, 2014 3:16 PM
    Friday, February 7, 2014 3:49 AM
    Moderator

All replies

  • re: deleting members of a collection

    Work from the end back to the start...
    '---
    Sub RemoveConnectionsR1()
      Dim i As Long
      For i = ActiveWorkbook.Connections.Count To 1 Step -1
        ActiveWorkbook.Connections.Item(i).Delete
      Next 'i
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Saturday, October 22, 2016 12:15 AM
    Tuesday, February 4, 2014 6:29 PM
  • re: deleting members of a collection

    Work from the end back to the start...
    '---
    Sub RemoveConnectionsR1()
      Dim i As Long
      For i = ActiveWorkbook.Connections.Count To 1 Step -1
        ActiveWorkbook.Connections.Item(i).Delete
      Next 'i
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    free Excel Date Picker add-in
    Install as an add-in; right-click any cell for menu
    (no ads, no trackers, no cookies, no registration)
    https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

    Thanks for the response!

    I get "invalid procedure call or argument" with that code and it doesn't delete the data connections.

    Also - did you mean to have the "i" commented out in the last line?

    Tuesday, February 4, 2014 7:48 PM

  • Re:  "I get "invalid procedure call or argument"
       No idea why

    Re:  "did you mean to have the "i" commented out"
       Yes
    '---
    Jim Cone
    Tuesday, February 4, 2014 9:29 PM

  • Re:  "I get "invalid procedure call or argument"
       No idea why

    Re:  "did you mean to have the "i" commented out"
       Yes
    '---
    Jim Cone
    Did you test your code on Excel 2013?  My code worked fine in Excel 2007/2010 so I'm wondering if somethings changed in 2013.
    Tuesday, February 4, 2014 9:50 PM
  • Re: "Never be first with the newest or last with the oldest"

    If you had mentioned xl2013, I most likely would have ignored your post.
    I do not have xl2013 and have no plans to acquire it.

    Each workbook in xl2013 opens in a new instance of Excel.
    One needs to specifiy the particular workbook, not just the "active" workbook.
    So something similar to the following is required...

    Application.Workbooks("workbookname").Connections.Count
    '---
    Jim Cone

    Tuesday, February 4, 2014 10:12 PM
  • Re: "Never be first with the newest or last with the oldest"

    If you had mentioned xl2013, I most likely would have ignored your post.
    I do not have xl2013 and have no plans to acquire it.

    Each workbook in xl2013 opens in a new instance of Excel.
    One needs to specifiy the particular workbook, not just the "active" workbook.
    So something similar to the following is required...

    Application.Workbooks("workbookname").Connections.Count
    '---
    Jim Cone

    Thank you and I will edit my title to reflect the version of Excel.

    Tuesday, February 4, 2014 11:03 PM
  • As James said you should delete the connections from back to start. As you delete, the ActiveWorkbook.Connections.Count() will reduce.
    If you still get the error message, please try the code below:
    Sub RemoveConnections()
       Do While ActiveWorkbook.Connections.Count > 0
          ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
       Loop
     End Sub


    Wednesday, February 5, 2014 9:35 AM
    Moderator
  • As James said you should delete the connections from back to start. As you delete, the ActiveWorkbook.Connections.Count() will reduce.
    If you still get the error message, please try the code below:
    Sub RemoveConnections()
       Do While ActiveWorkbook.Connections.Count > 1
          ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
       Loop
     End Sub

    Same error as above.
    Wednesday, February 5, 2014 3:06 PM
  • I've tested this code in excel 2013,it works fine.
    I get this error message only when I add a data Model when creating pivot table. It is a new in-memory analytics engine within excel 2013.

    Try to delete the related pivot table and then reopen your excel file to run this code.
    Also please check if tick the option 'prefer the Excel Data Model......'
    File->Options->Advanced->Data, prefer the excel data model... untick the option.


    • Edited by zkandyModerator Friday, February 7, 2014 3:50 AM
    • Marked as answer by nmuleski1 Monday, February 10, 2014 3:16 PM
    Friday, February 7, 2014 3:49 AM
    Moderator
  • I've tested this code in excel 2013,it works fine.
    I get this error message only when I add a data Model when creating pivot table. It is a new in-memory analytics engine within excel 2013.

    Try to delete the related pivot table and then reopen your excel file to run this code.
    Also please check if tick the option 'prefer the Excel Data Model......'
    File->Options->Advanced->Data, prefer the excel data model... untick the option.


    My worksheet does have a data model, but apparently I incorrectly assumed that this connection could be deleted like any other. 

    Do you know of any way to remove a Data Model with VBS?

    Friday, February 7, 2014 3:50 PM
  • Try this:
    Sub removeconnections()
    Dim xConnect As Object
    For Each xConnect In ActiveWorkbook.Connections
    If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
    Next xConnect
    End Sub
    

    • Marked as answer by nmuleski1 Tuesday, May 13, 2014 2:00 PM
    Monday, February 10, 2014 8:17 AM
    Moderator
  • That worked perfectly.  Thanks! 
    Monday, February 10, 2014 3:16 PM
  • Thanks!!
    Tuesday, May 13, 2014 4:35 AM
  • This works, Fantastic!
    Saturday, April 22, 2017 1:41 PM