Variable in OLE DB Connection

Hi All,

I have the below connection specified in the Data->Import External Data->Edit Query but I wish to replace the static SHADES799 with a variable Username that will pick up the Windows username of the person running the sheet. How and where can I make this change to achieve desired results?

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=d:\Documents and Settings\SHADES799\My Documents\Employee_RankingGrid.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 for any help,
Colm

Hi Colm

Perhaps:

Dim strUser As String, strConn As String

strUser = Environ$("Username")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=d:\Documents and Settings\" & strUser & "\My Documents\Employee_RankingGrid.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"

Richard

Assuming that the querytable is located on sheet1 of your workbook, in the workbook open event you could then include the code thus:

Private Sub Workbook_Open()
Dim strUser As String, strConn As String

strUser = Environ$("Username")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=d:\Documents and Settings\" & strUser & "\My Documents\Employee_RankingGrid.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"

Worksheets("Sheet1").QueryTables(1).Connection = strConn
End Sub

Note that you will need to refresh the querytable to view the data coming from the new connection.

Richard

Yes, pivot tables have a different object model to query tables, so you won't be able to use exactly the same code. Assuming Sheet1 contains your pivot table (amend as appropriate) can you place the following into a sub in a module in your workbook and run it and then copy and paste the contents of the new sheet created into your reply:

Dim sdArray, i
Set newSheet = ActiveWorkbook.Worksheets.Add
sdArray = Worksheets("Sheet1").UsedRange.PivotTable.SourceData
For i = LBound(sdArray) To UBound(sdArray)
    newSheet.Cells(i, 1) = sdArray(i)
Next i

Richard

Colm

Change just that line to:

sdArray = Worksheets("pivot").PivotTables(1).SourceData

EDIT: Sorry, didn't see your post. I didn't realise that restriction existed for OLE DB data sources. I will have to have a think on this.

No, I knew you were using OLE DB (as per your thread title - I just didn't know about the restriction when trying to access the SourceData property). I will raise a question with some knowledgeable people and see if they can suggest an alternative.

Richard