Using replace function to change database connection commandtext in vba macro

[excelnoob123456]

Hi everyone


I have a spreadsheet with a lot of tabs that looks up to a database and needs refreshing every month. However the command text has to be changed in each tab every month. I have worked out how to change the entire command text to a value in the spreadsheet but it changes the whole value of the command tex, really what I need to be able to do is to just get excel to replace certain text in the commandtext, i.e. If it finds 1456 to 1234 I could change it to something else. This is the code I have to change the values:

ActiveWorkbook.Connections("Database11").OLEDBConnection.CommandText = (Worksheets("Sheet1").Range("list2").Value)

I have tried using the following code to get it to replace but it just always gives me an error 'expected: =' I think because it needs an = after the commandtext - I tried doing it with strings as well but I can't work it out - any help greatly appreciated:

replace(ActiveWorkbook.Connections("Database11").OLEDBConnection.CommandText,"2345",Worksheets("Sheet1").Range("list2").Value)

 

[Jerry Sullivan]

Hi excelnoob123456,

I think you just need to assign that replace statement to the .CommandText property...

 With ActiveWorkbook.Connections("Database11").OLEDBConnection
   .CommandText = _
      Replace(.CommandText, "2345", Worksheets("Sheet1").Range("list2").Value)
 End With

 

[excelnoob123456]

Hi excelnoob123456,

I think you just need to assign that replace statement to the .CommandText property...

 With ActiveWorkbook.Connections("Database11").OLEDBConnection
   .CommandText = _
      Replace(.CommandText, "2345", Worksheets("Sheet1").Range("list2").Value)
 End With

Excellent!
Thanks very much that seems to work great, I'll try with the rest of the code tomorrow!

 

[excelnoob123456]

Excellent!
Thanks very much that seems to work great, I'll try with the rest of the code tomorrow!

Hi,

Thanks very much for that - that did work great. However, now I have another problem with the same piece of code - I'm not sure whether to post here or start a new thread for it but I will post here for now. 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(<wbr>Name).ODBCConnection
   .CommandText = _
      Replace(.CommandText, Worksheets("Update").oldvalue.<wbr>Value, Worksheets("Update").newvalue.<wbr>Value)
End With

 oldvaluestring = Worksheets("Update").oldvalue.<wbr>Value
newvaluestring = Worksheets("Update").newvalue.<wbr>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