Query Connection Strings and VBA Code to delete and connect them

[FrankDTank16]

I have this VBA code that i've been trying to modify. What it should do is, in this order:

Unprotect all sheets
Add Connections to all Queries (multiple databases and queries)
Refresh all queries
Remove all connections (so that the password's aren't accessible to normal excel users)
Protect all sheets (with password)
Goto Sheet that holds the button that runs macro

I'm getting an error when i run the add connections. Can anyone help me figure this out? VBA code below, i took out the full server name to protect my companies identity.

Sub AddConnectALL()
    'this will add the connection string to
    'the connections in this worksheet
   Connection1 = InStr(0, ActiveSheet.Connections, "cas_FRONGONE15_1", vbTextCompare)
   Connection2 = InStr(0, ActiveSheet.Connections, "cas_ProblemDB", vbTextCompare)
   Connection3 = InStr(0, ActiveSheet.Connections, "cas_FRONGONE", vbTextCompare)
If Connection1 >= 1 Then
ActiveSheet.Connections
        CN.ODBCConnection.BackgroundQuery = False 'this line is optional
        CN.ODBCConnection.Connection = _
        "ODBC;DRIVER={SQL Server}; Server=W8DFRONGONE\; Database=cas_FRONGONE15_1;" & _
        "UID=dba;PWD=Password;" & _
        "AnsiNPW=No" & _
        "DefaultIsolationLevel=READUNCOMMITTED"
ElseIf Connection2 >= 1 Then
ActiveSheet.Connections
        CN.ODBCConnection.BackgroundQuery = False 'this line is optional
        CN.ODBCConnection.Connection = _
        "ODBC;DRIVER={SQL Server}; Server=W8DFRONGONE\; Database=cas_ProblemDB;" & _
        "UID=dba;PWD=Password;" & _
        "AnsiNPW=No" & _
        "DefaultIsolationLevel=READUNCOMMITTED"
ElseIf Connection3 >= 1 Then
ActiveSheet.Connections
        CN.ODBCConnection.BackgroundQuery = False 'this line is optional
        CN.ODBCConnection.Connection = _
        "ODBC;DRIVER={SQL Server}; Server=W8DFRONGONE\; Database=cas_FRONGONE;" & _
        "UID=dba;PWD=Password;" & _
        "AnsiNPW=No" & _
        "DefaultIsolationLevel=READUNCOMMITTED"
End If
End Sub


Sub RemoveConnect()
    'this will remove the connection string from
    'all connections in the active workbook
    For Each CN In ActiveWorkbook.Connections
        CN.ODBCConnection.Connection = "ODBC;"
    Next
End Sub


Sub ListConnections()
    'this will list all query connection strings
    'in the active workbook
    For Each CN In ActiveWorkbook.Connections
        MsgBox (CN.ODBCConnection.Connection)
    Next
End Sub


Sub RefreshAll()
    'this will refresh all the data connections in the active workbook
    UnProtectAll
    AddConnect 'add the connection strings
    ActiveWorkbook.RefreshAll 'refresh all connections
    RemoveConnect 'remove the connection strings
    ProtectAll 'Protect all the sheets
    Application.Goto Reference:=Worksheets("Sheet1").Range("A1")
End Sub




Sub ProtectAll()
      ' Loop through all sheets in the workbook
      For i = 1 To Sheets.Count
         ' Activate each sheet in turn.
         Sheets(i).Activate
            ActiveSheet.Protect Password:="Sheets", DrawingObjects:=True, _
               Contents:=True, Scenarios:=True
      Next i
   End Sub




Public Sub UnProtectAll()
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:="Sheets"
Next
End Sub

 

[FrankDTank16]

bump

 

[FrankDTank16]

I would really appreciate any help. I usually don't try to build my own VBA's without internet help, but this one is really confusing me.

 

[FrankDTank16]

So is this impossible? Haven't seen any help for this yet.