[vba] Charging connection commandtext in many connections

[excelnoob123456]

Hi,

I have posted this on another thread but as it it is a different question to the one I started thread for I thought I would start another - if this is not allowed please let me know

Please see below the code that I have so far:

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

What I'm trying to do with this code is to get the connection text changed on all of the database connections that are named "Connection#number#' i.e. Connection1, Connection2 etc. Most of these numbers are between 1-100 however there are other numbers going up to the thousands and in total there are around 50-60 connections whose text needs changing.

Now - I've checked the code above and it does work if all of the database connections exist in the string - i.e if I make num just be 1 to 9 it works fine because there exists a connection named Connection1, Connection2 etc. up to connection 9. However, if I try to do 1 to 99 or 1 to 9999 it will come up with an error saying connection not found because not every number in that range is a connection, i.e. there is not a Connection67.

What I can't work out is how to get around this - ideally there would be a way to ask it to skip any that it can't connect to and just do those it can, however if this is not possible I was thinking that I could maybe use an array and manually type all of the connection names into this array then get the macro to cycle through all of these - I have created the array but I can't work out how to get it to cycle through all of the values within it.

By the way, I had to change OLEDBconnection to ODBCconnection because the oledb would not work. Also, if you could work out how to display a different message if the text is not found then that would be greatly appreciated but this is not vital.

Any further help greatly appreciated.

Many thanks

 

[excelnoob123456]

No one?

 

[excelnoob123456]

Any suggestions greatly appreciated. I'm nearly there but just need this last little bit to get the code working.

 

[sijpie]

Would this do the trick?

<font face=Calibri>    <SPAN style="color:#00007F">Dim</SPAN> oldvaluestring <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> newvaluestring <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> connection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>     <br>    connection = "Connection"<br>    <SPAN style="color:#00007F">For</SPAN> num = 1 <SPAN style="color:#00007F">To</SPAN> 9<br>        Name = connection & num<br>         <br>        <SPAN style="color:#007F00">' if connection does not exist, don't through up error, but skip to next</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NextNr<br>        <SPAN style="color:#007F00">'below replaces command text with contents of text boxes</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Connections(Name).ODBCConnection<br>           .CommandText = Replace(.CommandText, _<br>                    Worksheets("Update").oldvalue.Value, _<br>                    Worksheets("Update").newvalue.Value)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>NextNr:     <SPAN style="color:#007F00">' Go here if error</SPAN><br>        <SPAN style="color:#007F00">' Reset error behaviour</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        oldvaluestring = Worksheets("Update").oldvalue.Value<br>        newvaluestring = Worksheets("Update").newvalue.Value<br>    <br>    <SPAN style="color:#00007F">Next</SPAN> num<br>    <br>    <SPAN style="color:#007F00">'prints messagebox showing items replaced</SPAN><br>    MsgBox ("Process complete.  Old text """ & oldvaluestring & """ change to """ & newvaluestring & """ in database connection text requested, please check results")<br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[AlphaFrog]

What I'm trying to do with this code is to get the connection text changed on all of the database connections that are named "Connection#number#' i.e. Connection1, Connection2 etc. Most of these numbers are between 1-100 however there are other numbers going up to the thousands and in total there are around 50-60 connections whose text needs changing.

This isn't tested, but it should loop through all the connections and test if the name is like "Connection#number#' and then process only those connections.

[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] conn [COLOR=darkblue]In[/COLOR] ActiveWorkbook.Connections
    [COLOR=darkblue]If[/COLOR] LCase(conn.Name) [COLOR=darkblue]Like[/COLOR] "connection#*" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]With[/COLOR] conn.ODBCConnection
            .CommandText = _
                Replace(.CommandText, Worksheets("Update").oldvalue.Value, Worksheets("Update").newvalue.Value)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    oldvaluestring = Worksheets("Update").oldvalue.Value
    newvaluestring = Worksheets("Update").newvalue.Value
    
[COLOR=darkblue]Next[/COLOR]

 

[excelnoob123456]

Thanks for the reply,

I gave this below but it gave me some error messages as shown below (some sensitive info cut out)

This pops up a few times, if I cancel through it comes up with:

If I login it will popup again, I login again then this pops up:

And when I cancel through this it does the same thing (asks me to login comes up with the above window) then when I cancel it comes up with this:

 

[excelnoob123456]

Thanks very much, I'll try this tomorrow and let you know what happens

 

[excelnoob123456]

Ok, i tried the code you suggested and unfortunately got the same errors as above. I think the answer might be to type all of the names of the connections manually then add them to an array and get the replace function to loop through that, I have created the array but can't work out how to get the replace function to loop through it

 

[sijpie]

Have you tried my suggestion yet?

 

[excelnoob123456]

Have you tried my suggestion yet?

Hi sijpie

Yes I have, it comes up with the errors given above unfortunately same as other solution offered. The only thing I can think of is typing the connection names manually and putting them in an array then getting the replace function to cycle through the array. I have typed them up and created the array but I can't work out how to get the replace function to cycle through it unfortunately