Excel: problem with deleting .Connections in a protected workbook

Question

While trying to remove external connections from my protected Workbook (which imports data from a csv file) with VBA, I run into Runtime error '5'. Strangely, it works if I unprotect the Workbook.

I found this thread: Protect Excel Worksheet For Read Only But Enable External Data Refresh, but the proposed solution of unprotecting the workbook while the scripts are running is out of the question.

Here is the code that I use to remove the external data connections:

Sub RemoveExternalDataConnections()

    Dim i As Long

    For i = ActiveWorkbook.Connections.Count To 1 Step -1

        ActiveWorkbook.Connections.Item(i).Delete

     Next

End Sub

All in one, I would like the external data connections be removed even if the workbook is protected.

Thomas J. Thomas J. · Accepted Answer · 2019-08-24T09:20:29

For anyone else - it seems that unprotecting the workbook is the easiest way. Setting up a password on a document/VBA project is not particularly safe anyway and it will not block most tech savvy users from accessing it.

Excel: problem with deleting .Connections in a protected workbook

1 Answers