Change SQL with VBA

[sh1pley]

I have to change the SQL on hundreds of queries. This involves changing the server, doing a find and replace on the command text which I have managed to do with the below code.
However I also need to add a line onto the command text.... but if this line already says TRANTYPE='SEP06' then I need to leave it as it is.

New line to add:
AND (D_DETAILS.TRANTYPE='DEFAULT'))

Sub ChangeSQL()

Dim OldConn As String
Dim NewConn As String

OldConn = "ODBC;DRIVER=SQL Server;SERVER=BLISS001;UID=report user;PWD=reportuser;APP=Microsoft Office XP;WSID=BLISW016;DATABASE=live;Network=DBNMPNTW;"
NewConn = "ODBC;DRIVER=SQL Server;SERVER=BLISSSQL01;UID=report user;;APP=Microsoft Office XP;WSID=BLISW016;DATABASE=DREAM;"

Sheet5.QueryTables.Item("Query3").Connection = Replace(Sheet5.QueryTables.Item("Query3").Connection, OldConn, NewConn)

Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "live", "DREAM")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_APBUDGET", "M_BALANCE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "BUDGETCODE", "TRANTYPE")
Sheet5.QueryTables.Item("Query3").CommandText = Replace(Sheet5.QueryTables.Item("Query3").CommandText, "M_BALANCE2", "M_BALANCE")

Sheet5.QueryTables.Item("Query3").Refresh

End Sub

 

[NateO]

Hello,

How about testing the CommandText Property to see if contains your target String with the Instr() function? See the following:

http://msdn2.microsoft.com/en-us/library/8460tsh1(VS.80).aspx

You could apply this test with an If Statement.