how to change userid/password in multiple queries simultaneously

[gcaglion]

I have a workbook with several queries to an external db. For all of them, I let Excel save both UserId and Password (not a big security concern, at the moment).
Quite often I need to run the same set of queries against different db schema, that is, by logging in using different UserId/Password (s).
Is there a way (even with VBA) to set the same UserId/Password for all my queries simultaneously , without having to modify each query's properties?

Thanks in advance

 

[mole999]

go global declaration

 

[gcaglion]

Thanks for your reply. However, I don't see how global declaration (of what?) could accomplish what I'm looking for.
Anyhow, After some tries, I put together the following VBA code, that seems to do what I need.<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub UpdateCredentials(ByVal userid As String, ByVal userpwd As String)

Dim ConnString As String
Dim newString As String
Dim ws As Worksheet

For Each ws In Worksheets
For o = 1 To ws.ListObjects.Count
ConnString = ws.ListObjects(o).QueryTable.Connection
i1 = InStr(1, ConnString, "UID", vbTextCompare)
i2 = InStr(1, ConnString, "PWD", vbTextCompare)
If i2 = 0 Then i2 = i1 + 2
i3 = InStr(i2, ConnString, ";", vbTextCompare)

newString = Left(ConnString, i1 + 3) + userid + ";PWD=" + userpwd + ";" + Right(ConnString, Len(ConnString) - i3)

ws.ListObjects(o).QueryTable.Connection = newString

Next o
Next</code>End Sub