[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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top