I have hundreds of external data sources that need changing to a new server
To do this manually I have to:
Edit Query - Change Server
Change the database from Live to Dream
OK
Then I need to change the SQL to replace
live with dream
plus a few others as seen in the code below
Then I return the data to excel
This is the code I have tried to come up with. It seems to pass through all the lines but when it gets to the refresh statement it brings up the box in excel that asks for the server and database name again???
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("Query2").Connection = Replace(Sheet5.QueryTables.Item("Query2").Connection, OldConn, NewConn)
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('live')", "WHERE ('DREAM')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_APBUDGET')", "WHERE ('M_BALANCE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('BUDGETCODE')", "WHERE ('TRANTYPE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_BALANCE2')", "WHERE ('M_BALANCE')")
Sheet5.QueryTables.Item("Query2").Refresh
End Sub
To do this manually I have to:
Edit Query - Change Server
Change the database from Live to Dream
OK
Then I need to change the SQL to replace
live with dream
plus a few others as seen in the code below
Then I return the data to excel
This is the code I have tried to come up with. It seems to pass through all the lines but when it gets to the refresh statement it brings up the box in excel that asks for the server and database name again???
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("Query2").Connection = Replace(Sheet5.QueryTables.Item("Query2").Connection, OldConn, NewConn)
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('live')", "WHERE ('DREAM')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_APBUDGET')", "WHERE ('M_BALANCE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('BUDGETCODE')", "WHERE ('TRANTYPE')")
Sheet5.QueryTables.Item("Query2").CommandText = Replace(Sheet5.QueryTables.Item("Query2").CommandText, "WHERE ('M_BALANCE2')", "WHERE ('M_BALANCE')")
Sheet5.QueryTables.Item("Query2").Refresh
End Sub