Vba to refresh data connection

[pedie]

Hi, can Vba refresh data connection with password?
I tried myself different ways but didnt know what to do...i mean the right way...I also googled but no luck.

Please advice.

[/FONT]
[FONT=Courier New]Option Explicit
Sub refresh()
    With ActiveSheet.PivotTables("PivotTable1")
    .Connection.Password = "123"
    .PivotCache.refresh
    
    End With
End Sub

 

[pedie]

This way it refreshes the connection but however ask for password...
Please advice, where do i input the password so that it does not prompt for it...

Thanks again

[/FONT]
[FONT=Courier New]ActiveWorkbook.Connections("budget data").refresh

 

[ternopil]

Hi Pedie,

just found your post today. You could set your password by adding

PWD=YourPassword

into Connection String in the Connection Properties window and then check Save Password checkbox. And also you have to click Yes in the pop-up dialog window with warning that password will be saved without encryption.

 

[pedie]

Ternopil, thanks...the code way...how exactly do we code to have a password entered to refresh?

 

[ternopil]

Pedie,

You have to refresh the actual data connection (PivotTable will work only when you have PT as query output - however I'm not sure)

And you have to change your external data connection string to have password included in it (or message box asking for the password) - in case when you have your external data query created with vba code. You cab get some ideas here - http://stackoverflow.com/questions/...ta-query-connections-e-g-point-from-one-datab

 

[pedie]

Ternopil, it helps...thanks once again for helping.

 

[ternopil]

Pedie,
You are welcome! I've recently learned this external data connections from Excel and using in my reports

 

[noppojp]

For what it's worth, i had to do the following to get an SP list refresh in my Excel form template.
i put it in my Workbook_Open() statement:

code/
ActiveWorkbook.Connections("owssvr[1]").refresh
/code

The name of the connection is NOT the name of my worksheet, but the actual name of the connection which can be gotten by going into the properties of the connection and copying the name.

This method fits my design perfectly because anyone can use the Excel template i have, but only 10 people can access the SP List to make changes. The changes they make apply to every new instance of the Excel form. So, every time the excel sheet is opened, the SP List is refreshed with the latest values from the SP List having updates by the 10 people.

For those of you wanting a 1 way update (from SP to Excel), this little one-liner code snippet helped me greatly.

Noppojp
Tokyo, Japan

 

[svendiamond]

You could set your password by adding PWD=YourPassword into Connection String in the Connection Properties window and then check Save Password checkbox. And also you have to click Yes in the pop-up dialog window with warning that password will be saved without encryption.

I'm trying that right now and it doesn't seem to work. As soon as I click OK and go back to the properties window, it is gone! Any idea what am I doing wrong?