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

Anyone else have any ideas?

Just to clarify - the reason that it is static text is that the commandtext mostly needs to stay the same - there are only a few queries that need changing.

no-one?

 

[excelnoob123456]

no-one?

anyone?

 

[sijpie]

You'll be getting more response if you start a new thread on this (also with a link to this current thread). Try to describe your problem even more clearly. What does work, what doesn't work.

 

[excelnoob123456]

You'll be getting more response if you start a new thread on this (also with a link to this current thread). Try to describe your problem even more clearly. What does work, what doesn't work.

Thanks for the advice, I'll try that now.