[vba] Changing connection commandtext in many connections

[excelnoob123456]

Hi everyone

I have a spreadsheet which has many connections to various databases, every month one line of the command text in a large number of these connections needs to be changed, i.e. from 'between 1222 and 5222' to 'between 5223 and 7234'. Currently this is done manually which is quite time consuming therefore I am trying to build a macro that will replace all of the values at once, so you would only have to enter the old values and values to replace once and it would replace over all commandtexts in which it finds these values. The database connections are number from 1 to around 6000, but (and this is a key point) they are not 1-6000 inclusive (i.e. there are not 6000 database connections), for example there is a connection1, connection5, connection1000, connection 1004, etc.

I have written the below code, which uses a next function to cycle through the connection names trying to replace each text. This does work if all of the connection names in the range exist (e.g. connections 1-9) but if I change the connections to 1-6000 it will give an error when it comes to a connection name that does not exist (e.g. connection66)

Dim oldvaluestring As String
Dim newvaluestring As String
Dim num As Integer
Dim connection As String
 
connection = "Connection"
For num = 1 To 9
Name = connection & num
 
'below replaces command text with contents of text boxes
With ActiveWorkbook.Connections(Name).ODBCConnection
   .CommandText = _
      Replace(.CommandText, Worksheets("Update").oldvalue.Value, Worksheets("Update").newvalue.Value)
End With

 oldvaluestring = Worksheets("Update").oldvalue.Value
newvaluestring = Worksheets("Update").newvalue.Value

Next num

'prints messagebox showing items replaced
MsgBox ("Process complete.  Old text """ & oldvaluestring & """ change to """ & newvaluestring & """ in database connection text requested, please check results")
 
End Sub

I had created another thread here: https://www.mrexcel.com/forum/excel...-connection-commandtext-many-connections.html about this and had a lot of helpful suggestions but unfortunately none that worked. I think the most promising response I had was the following which would try to connect to all of the connection names which I have typed into a selection of cells, however, unfortunately this code gives me a subscript out of range error.

Sub ArrayRead()
    Dim v
    Dim i As Integer
    Dim rR As Range, rC As Range
    
    'load the connection names from range in spreadsheet
    
    Set rR = Range("R2").CurrentRegion
    
    ' set array size to number of rows
    ReDim v(1 To rR.Rows.Count)
    i = 1
    'copy each cell into the array
    For Each rC In rR
        v(i) = rC.Value
        i = i + 1
    Next rC
    
    ' Use the array
    For i = 1 To UBound(v)
        MakeConnection (v(i))
    Next i
    
End Sub


Sub MakeConnection(sConnName As String)
    Dim sCommandT As String
    
    sCommandT = "Some initial string"
    
    With ActiveWorkbook.Connections(sConnName).ODBCConnection
       .CommandText = _
          Replace(sCommandT, Worksheets("Update").oldValue.Value, Worksheets("Update").newValue.Value)
    End With
    
    
End Sub

Hopefully someone more intelligent than me will have an idea as to how this can be achieved? Very grateful for all responses.

 

[wideboydixon]

Can you just iterate through the connections rather than relying on name?

Public Sub UpdateConnections()

Dim oldValueString As String
Dim newValueString As String
Dim i As Long

If ActiveWorkbook.Connections.Count = 0 Then Exit Sub

oldValueString = Worksheets("Update").oldvalue.Value
newValueString = Worksheets("Update").newvalue.Value

For i = 1 To ActiveWorkbook.Connections.Count
    With ActiveWorkbook.Connections(i).ODBCConnection
        .CommandText = Replace(.CommandText, oldValueString, newValueString)
    End With
Next i

MsgBox "Process complete. Old text """ & oldValueString & """ changed to """ & newValueString & """ in database connection text requested, please check results"

End Sub

WBD

 

[excelnoob123456]

Can you just iterate through the connections rather than relying on name?

Public Sub UpdateConnections()

Dim oldValueString As String
Dim newValueString As String
Dim i As Long

If ActiveWorkbook.Connections.Count = 0 Then Exit Sub

oldValueString = Worksheets("Update").oldvalue.Value
newValueString = Worksheets("Update").newvalue.Value

For i = 1 To ActiveWorkbook.Connections.Count
    With ActiveWorkbook.Connections(i).ODBCConnection
        .CommandText = Replace(.CommandText, oldValueString, newValueString)
    End With
Next i

MsgBox "Process complete. Old text """ & oldValueString & """ changed to """ & newValueString & """ in database connection text requested, please check results"

End Sub

WBD

Thanks for the response WBD

So when I try this it comes up with a message asking for Parameter 1 - I tried typing in the name of connection and it came up again and kept coming up, when I cancel it says 'run time error' application defined or object defined error. Then when I check the connection text of the name I typed in it hasn't changed anyway...

 

[wideboydixon]

Which line is causing the error? It should be highlighted when you get the error.

WBD

 

[excelnoob123456]

It's the following:

     .CommandText = Replace(.CommandText, oldValueString, newValueString)

Thanks for your help

 

[excelnoob123456]

Anyone else have any ideas?