Friday, August 14, 2009

Changing Excel query connection strings

We use Excel and database queries extensively for reporting purposes. It's quick and easy to set-up, and provides reports that our clients can manipulate.

However I have recently run into a rather painful excel quirk with ODBC connections: Excel stores the database connection string internally. Even if you change the ODBC connection on the computer, excel still uses the original connection from when the query was created.

This bit us when moving the reports to a different machine. Despite having the same ODBC connection set up, excel wouldn't refresh the query and gave the error "[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

Aside
A similar problem can happen if you update the database from sql server 2000 to sql server 2005 or 2008. In that case you may get the error "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON." In that case, you need to alter the connection string to change "APP=Microsoft® Query" to something else. Apparently MS hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason. I suggest "APP=WTFWYT"


Changing one query
(from here)
"Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the following in front of the string:
ActiveCell.QueryTable.Connection =
"

Changing multiple queries in a spreadsheet
(modified from here)
You need to create the following macro (change connection string to suit, see above) and run it in each spreadsheet requiring change. (See the steps below). Once the macro has run successfully, delete it before saving the spreadsheet.

Sub ChangeConnections()
Dim sh As Worksheet, qy As QueryTable
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = "..."
On Error Resume Next
qy.Refresh
If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
Next qy
Next ws
End Sub

Getting the new connection string
The easiest way to find the new connections string is to create a new query and do the ?ActiveCell.QueryTable.Connection trick.

14 comments:

Anonymous said...

Hi,

very useful, but any hints for how to cope with Pivot Table links where you don't have an 'ActiveCell' to lookup the connection string?

Sean said...

Sorry, I have no idea how to do it for pivot tables. There is probably a connection property on the pivot table, but I don't know the details.

Bowerbird said...

V useful. Thanks.

David said...

Thanks Sean for the macro for changing connection strings. It was very helpful!

Unknown said...

Looking for a similar problem for pivot tables I found following solution from Microsoft:
http://support.microsoft.com/kb/816562/

Anonymous said...

I try the ? ActiveCell.QueryTable.Connection command and I get a runtime error 1004 application error message.

Brian said...

God bless you, that just saved us a lot of time. Why in the world would Microsoft make a fix such as this so obscure?

To the previous Anonymous poster, what version are you using? I used this in 2000 and it worked perfectly. For 2007 you can change them directly within Excel by going to Data -> Connections -> Connections.

Anonymous said...

Thanks a lot for this explanation and solution - saved many hours.

Robot Tim said...

Preface a web query string with "URL;". For Yahoo stock and index data, as an example: ActiveCell.QueryTable.Connection =
"URL;http://finance.yahoo.com/d/quotes.csv?s=^gspc+^n225+^HSI+^JKSE+CF+THD&f=sl1d1t1c1ohgrr6r7r5k1r2a2v&e=.csv"
To make it so I don't have to edit the query to change what stocks I want to look at, I put the sting data in cell A1 and change the code to: ActiveCell.QueryTable.Connection = Range("A1").Value

I found the URL; example at http://www.jkp-ads.com/articles/webquery.asp?AllComments=True

Robot Tim

Anonymous said...

There is probably a connection property on the pivot table, but I don't know the details.
Microsoft Excel Training Ny

Anonymous said...

For Office 2013, you need to access the connection string from this property:

ActiveCell.ListObject.QueryTable.Connection

Anonymous said...

For Pivot table you have to select the pivot and type Immediate window:
? Selection.Pivottable.PivotCache.Connection
Then to change the connection (as Robot Tim already mentioned that method) put new conection string in cell A1 and in Immediat window type:
Selection.Pivottable.PivotCache.Connection=ActiveSheet.Range("A1").Value

Anonymous said...

You can always try using a tool such as www.officefindreplace.com to accomplish this.

Anonymous said...

Still a live saver blog in 2017 :-)