myyis
asked on
Change the path of the data source in my Excel pivot table with macro
With a macro I need to change the path of the data source in my Excel pivot table
\Users\James\Downloads[exa mpleexcel. xls]
to:
\Users\currentuser\Downloa ds[example excel.xls]
Please note that [exampleexcel.xls] is also dynamic.
Anybody can help me? Thank you very much.
\Users\James\Downloads[exa
to:
\Users\currentuser\Downloa
Please note that [exampleexcel.xls] is also dynamic.
Anybody can help me? Thank you very much.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With a manupulation I have solved
Thank you
Myfolder = Environ("Username")
MyPivotsrc = ActiveSheet.PivotTables("P ivotTable1 ").SourceD ata
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("P ivotTable1 ").ChangeP ivotCache ActiveWorkbook. _
PivotCaches.Create(SourceT ype:=xlDat abase, SourceData:= _
MyPivotsource, _
Version:=xlPivotTableVersi on15)
Thank you
Myfolder = Environ("Username")
MyPivotsrc = ActiveSheet.PivotTables("P
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("P
PivotCaches.Create(SourceT
MyPivotsource, _
Version:=xlPivotTableVersi
Open in new window
Would this be what you are looking for?