Pivot table access - Connection

[Candyman]

I've created a connection to a Analysis Server where my cube is. Then
created 6 pivot tables in one spreadsheet pointing to 'MyCube'. I have a
small macro to refresh the tables, but when I give the XLS to another user
they are prompted with user ID and passwords for each Pivot table.

How do I at least cut the prompts down to once or gather the users LAN ID
from their computer to correct the connection?

Thank you

 

[ShaneDevenshire]

Hi Candyman,

You didn't tell us what version of Excel you are running and I don't work
with Analysis Server so this is just an idea:

Select a pivot table and choose PivotTable, Table Options, and turn on Save
Password.

 

[Candyman]

I am using Excel 2003 currently against a SQL Server 2005.

I hav no problem connecting, but when others connect they are prompted for a
connection for each table.
I am toying with having a form to reeturn the Login and Password, then
refresh:
frmLogin.Show
myUID = frmLogin.txtLogin
myPWD = frmLogin.txtPassword

' Not sure of the correct string ( used from other code)
'****** Set Connection String
ConnectString = "ODBC;DSN=MyCube" & ";UID=" & myUID & ";PWD=" & myPWD &
";DBALIAS=MyCube;TXNISOLATION=1"


'******** Refresh the data **********************************


For Each WS In Worksheets
For Each PT In WS.PivotTables
. pt.Add(Connection:=ConnectString) .RefreshTable
Next PT
Next WS

Not sure what to do on line 3 nor of the connection string.