?
Solved

Change the path of the data source in my Excel pivot table with macro

Posted on 2016-10-03
4
Medium Priority
?
104 Views
Last Modified: 2016-10-04
With a macro I need to change the path of the data source in my Excel pivot table

\Users\James\Downloads[exampleexcel.xls]

to:

\Users\currentuser\Downloads[exampleexcel.xls]

Please note that [exampleexcel.xls] is also dynamic.

Anybody can help me? Thank you very much.
0
Comment
Question by:myyis
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:Koen
ID: 41827124
    MyPivotSource = "\Users\currentuser\Downloads\" & MyFilename
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MyPivotsource, Version _
        :=xlPivotTableVersion15)

Open in new window


Would this be what you are looking for?
0
 
LVL 1

Author Comment

by:myyis
ID: 41827169
Hi Koen,
Thank you for your answer, I see that  my question is not clear enough.
It looks good but the  "currentuser"  is also dynamic.
"currentuser" should  be the current windows user logged in.
0
 
LVL 9

Accepted Solution

by:
Koen earned 2000 total points
ID: 41827231
Myfolder = Environ("Username")
MyPivotSource = "\Users\" & Myfolder & "\Downloads\" & MyFilename
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MyPivotsource, Version _
        :=xlPivotTableVersion15)

Open in new window

0
 
LVL 1

Author Comment

by:myyis
ID: 41828079
With a manupulation I have solved
Thank you

Myfolder = Environ("Username")
MyPivotsrc = ActiveSheet.PivotTables("PivotTable1").SourceData

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String


openPos = InStr(MyPivotsrc, "Users\")
closePos = InStr(MyPivotsrc, "\Downloads")
midBit = Mid(MyPivotsrc, openPos + 6, closePos - openPos - 6)
MyPivotsource = Replace(MyPivotsrc, midBit, Myfolder)
MyPivotsource = Replace(MyPivotsource, "'", "")
MyPivotsource = "C:" & MyPivotsource



ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        MyPivotsource, _
        Version:=xlPivotTableVersion15)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question