Changing multiple data connection string in excel

Question

I have 9 sheets that connect to different tables in teradata, each time i refresh it will ask me for my userid and PWD. I end up entering it 9 times.

I am not that familiar with VBA but I have written a VBA code to change connection string for a spreadsheet with one data connection. (Please find below the code i use).

When i use this VBA code to change the connection string for the spreadsheet with 9 connections i am getting "Teradata database connect Pop - Up" and have to enter my user id and PWD again.

Could someone please explain why am i getting this pop - to enter my password or please provide a VBA code to change connection string in excel using VBA or please tell me if i am doing some wrong.

Thanks Syam

Sub UPDATE()

Dim ct As String
Dim connect
Dim ncon As String
Dim nconr As String

DSN = "GDWPROD2"
'this checks number of data connection
ct = ActiveWorkbook.Connections.Count

While ct > 0
Set i = ActiveWorkbook.Connections.Item(1)
connect = i.ODBCConnection.Connection
'checks for user id and password
'CheckCredentials
ncon = "ODBC;" & "DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD & ";" & "DATABASE=PROF_LEADS_VERDE;"
i.ODBCConnection.Connection = ncon
ct = ct - 1
Wend

 ActiveWorkbook.RefreshAll

End Sub

PermaNoob PermaNoob · Accepted Answer · 2013-11-25T17:54:53

You need to Dim both the Username and Password as strings and then set them. You are already calling them, but you haven't defined them anywhere. (Unless you left that out when you posted?)

Either that, or you can not call them, and it should verify whether you have permissions to connect automatically. In order for it to do that, though, you'll completely leave out the username and password and put in Trusted_Connection=Yes; instead.

Changing multiple data connection string in excel

1 Answers