macro with user input

[scottc_00]

I have several excel tools that use pivot tables to get and process data from Access. THese tools have multiple (many) pivot tables. This becomes a burden when I need to refresh the data in the pivot tables. so I use the macro

Sub Refresh_All_PT_WS() 'Refresh all pivot tables active worksheet
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub

and it works great. However my data is password protected (in Access) so once I start the macro it asks me to input the password for every table as it updates.

NOw the question...Does anyone know a way to use an input box to ask for the password once when the macro starts (simple enough) and use it throughout the macro as it loops and apply it when the password box appears (the hard part)? Alternately since this macro is only in my "PERSONAL" workbook is there a way to insert the password directly into the macro loop (without user input) so it is applied automatically each time the request appears?

Thanks

 

[wongm003]

If you are using the pivot table with external data, is there a reason you cannot check the Pivot Table options - save password under external data options?

 

[scottc_00]

The reason is..I didn't know about it. That works like a charm. Thanks a million.

 

[scottc_00]

hmmm...upon further review...this method apparently poses an unacceptable security risk. While investigating this option I learned that, "...This option saves the password as readable text..." not exactly sure where it saves it yet but I can't have this password readable. The actual file is also password protected so i am not worried about others refreshing the data as they cant save the file anyway but I can not give them access to the raw data.

Looks like i am back to the drawing board as far as a macro option needed.

Thanks anyway.