Pivot Refresh over ODBC

[Guest]

I have a series of workbooks containing 2 or more Pivot Tables. Each table
refreshes from an external source over an ODBC connection. The user is
prompted for username/password for each table refresh. I'd like to have a
UserForm capture the username/password once and then code VBA to supply it on
each refresh.

I've found this snipet of code (thanks to this forum), but do not know how
to proceed.

Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Dsn=myDsn;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

Any thoughts on how to do this? Thanks in advance!!!

My current code:
For Each sht In ActiveWorkbook.Worksheets
For i = 1 To sht.PivotTables.count
Set pvt = sht.PivotTables(i)
pvt.RefreshTable
Next i
Next sht

 

[GregR]

Cinque, try this

"ODBC;DSN=XXX;Description=XXX;UID=userID;PWD=Password;APP=Microsoft
Office 2003;WSID=XXX;DATABASE=Intranet;LANGUAGE=us_english
Supply your own parameters.

Greg

 

[Guest]

Yes, Greg, I think you are right. I'm just not clever enough to code this.

I did the following:
Debug.Print ActiveWorkbook.PivotCaches(i).Connection
Which yielded:
ODBC;DSN=XXX;UID=XXX;DBQ=XXX;ASY=OFF;

My unsucessful attempt at coding is:
ActiveWorkbook.PivotCaches(i).MakeConnection
Connection:="ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;ASY=OFF;"

I really appreciate any help you can give me...

 

[GregR]

Cinque, what I did, was turn on the macro recorder and then manually go
through all the steps you normally do to connect. This should produce a
connection string with all your defaults, except the PW. You can then
modify the connection string with your password. HTH

Greg

 

[Guest]

Thanks, Greg. I had tried the macro recorder early on. For some reason it
did not return the code. I did find this code in VBA help by searching on
connection. And it works!!! Thanks for all your assistance!!!

With ActiveWorkbook.PivotCaches(i)
.Connection = _
"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;ASY=OFF;"
.Refresh
End With