Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Refresh over ODBC
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Refresh over ODBC
Cinque, try this
"ODBC;DSN=XXX;Description=XXX;UID=userID;PWD=Passw ord;APP=Microsoft Office 2003;WSID=XXX;DATABASE=Intranet;LANGUAGE=us_englis h Supply your own parameters. Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Refresh over ODBC
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" wrote: Cinque, try this "ODBC;DSN=XXX;Description=XXX;UID=userID;PWD=Passw ord;APP=Microsoft Office 2003;WSID=XXX;DATABASE=Intranet;LANGUAGE=us_englis h Supply your own parameters. Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Refresh over ODBC
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Refresh over ODBC
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 "GregR" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated ODBC data refresh question. | Excel Discussion (Misc queries) | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
Refresh spreadsheet from database through ODBC | Excel Programming | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |