Refreshing Pivot tables link to a SQL Server

D

dins45768

I have 2 pivot tables in an EXCEL workbook that are linked to tables located
on a SQL Server. The end user will be responsible for refreshing these tables
by clicking a button. Right now a dialog box appears and requires the user to
enter an id and password for each table. I want to have the pivot tables
refresh on open without prompting for a password. Is this possible.

I have started to write the following code but I don't know if I am on the
right track.

Sub Refresh_Data()
'Set up the connection string, reference an ODBC connection
connstring = _
"ODBC;DSN=Gulf States SQL Server;UID=e45768;PWD=e45768;Database=DataMart"
'Refresh Pivot Tables
Worksheets("PvtTbls").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("PvtTbls").PivotTables("PivotTable2").PivotCache.Refresh

End Sub

I am a programming novice and not sure how to make this work. Any help would
be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot tables not refreshing after data source update 1
Using macro to switch 'page' in a pivot table? 1
Programmatically setting pivot sourcedata 0
Refreshing pivot tables 0
Pass a command text in pivot table 0
Update Pivot page field from a cell entry 0
Refreshing pivot table from different worksheet 0
Child pivot table's source breaking from parent pivot table 0

Top