Change external data source depending on username

Hi, could anyone advise how to automatically change the external data source for a pivot table according to the username of the person logged in to Windows?

I need to use two separate excel files: one containing the pivot table and a separate file containing the source data. Both files will exist in the user's My Documents folder. I need to distribute the pivot table file to multiple users. When they open the pivot table file, the data source needs to be their own "...\My Documents\source data.xls" How can this be achieved?

Many thanks in advance,

Jack.

Welcome to the forum!

For the MyDocuments part:

Please Login or Register  to view this content.

Kenneth, thanks for your welcome and response. Apologies - I'm only a novice vb writer who tweaks macros I've recorded. How should I utilise the macro you suggest?

I have an Auto_open sub which refreshes the pivot table and enters values against certain page field items. I assume I need to insert a preliminary step (before the refresh) to point to the new external datasource, i.e., to the source data file in the user's own My Documents folder. Could you suggest what that code should be so that it changes the username in the Connection details of the pivot table's Get Data / Edit OLE DB Query dialog box?

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Documents and Settings\username\My Documents\sourcedata.xls;Mode=ReadWrite;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Many thanks,
Jack.

I don't work with pivot tables much so an example xls would help me help you.

I would think that you could record a macro to modify the connection string and modify the code from there.

Answer provided at:
http://excelusergroup.org/forums/t/1246.aspx