Coding UserInfo for ODBC Connection - Pivot Table Refresh

[krizma5683]

I'm trying to find out if there's a way to code the user name and password that needs to be entered when trying to refresh a pivot table that is built from an ODBC datasource?

I've written some code to refresh my pivot table, but I get an error when I try to refresh & I suspect it's becuase it can't find the user login information to the datasource.

This is my code:

Application.ScreenUpdating = False
    Sheets("External Data").Visible = True
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Material Costs").Visible = True
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Calculator").Select
    Range("A2").Select
    Application.ScreenUpdating = True

Thanks!

 

[SkipVought]

Hi,

Turn on your mcaro recorder.

Record editing your PT, by activating the wizard, and hitting the [Previous] button to get to the External Data - Edit and get out.

Your generated code will include the connnect string, with null user and password probably.

You can modify this string on the fly if you wish.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[krizma5683]

Thanks - that did the trick!

 

[krizma5683]

Now I'm wondering, is there a way to pass the login and password information from one array to the next in the case of multiple pivot tables that need to be refreshed.

I don't want to hard code the user name & password, but I don't want to make the user type it multiple times.

 

[SkipVought]

If you assign the connect string to a variable, then you can use that variable in each PT.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}