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