[vba] Changing connection commandtext in many connections

excelnoob123456

New Member
Joined
Mar 7, 2015
Messages
39
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)

Rich (BB code):
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.

Rich (BB code):
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Can you just iterate through the connections rather than relying on name?

Code:
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

New Member
Joined
Mar 7, 2015
Messages
39
Can you just iterate through the connections rather than relying on name?

Code:
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Which line is causing the error? It should be highlighted when you get the error.

WBD
 

excelnoob123456

New Member
Joined
Mar 7, 2015
Messages
39
It's the following:

Code:
     .CommandText = Replace(.CommandText, oldValueString, newValueString)
Thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,090,461
Messages
5,414,651
Members
403,541
Latest member
J0hnJ

This Week's Hot Topics

Top