How to change an Excel Pivot Table source to the newest file in a folder, and then update all the other pivot tables

I have an Excel file with multiple pivot tables.  I need to be able to change the data source for each of them to be the most current file in a specified folder on my network.

I think I've got the first part, finding the most current file, working.  But I'm hung up on how to update one (or all) of the pivot tables in the file.


Sub GetCurrentDataFile()

   Dim fso As Object, fld As Object, fil As Object
    Dim TestDate As Date
    Dim MostRecentCreatedFile As String
    Dim FolderPath As String

    FolderPath = "\\nc-files.mynatc.us\PSREPORT\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FolderPath)
    
    For Each fil In fld.Files
        If fil.DateCreated > TestDate Then
            TestDate = fil.DateCreated
            MostRecentCreatedFile = fil.Name
        End If
    Next

    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing

ActiveWorkbook.Sheets("Admin Update").Activate

' ---- here is where it stops working-----------------

PivotTable1.Select
PivotTable1.SourceData = FolderPath & "[" & MostRecentCreatedFile & "]sheet1'!$A$3:$P$65536"


   For Each Pivot In ThisWorkbook.PivotCaches
      Reference = Pivot.SourceData
      ' Reference contains the path to the pivot table's data source such as
      '\Documents and Settings\Your Name\Desktop\Book1.xls'!TestRange
      ' To change the directory we need to find the last backslash
' '     Position = InStrRev(Reference, "\")
      Pivot.SourceData = FolderPath & MostRecentCreatedFile & "\" & Right(Reference, Len(Reference) - Position)
   Next

ErrHandler:
MsgBox Err.Number & ":" & Err.Description

End Sub

Open in new window

BEBaldaufAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
You need something like:

For Each pt In ws.PivotTables
            pt.PivotCache.Connection = _
                  Application.Substitute(pt.PivotCache.Connection, _
                  OldPath, NewPath)
            On Error Resume Next
            pt.PivotCache.CommandText = _
                  StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
                  OldPath, NewPath))
            pt.PivotCache.Refresh
       next pt

Open in new window


See this link:  http://support.microsoft.com/kb/816562

Note the specific code in the tip (which I pulled out quickly, above) around traversing pivot tables and caches and changing folders from old folder to new folder.  This works!

Dave
0
BEBaldaufAuthor Commented:
I tried to modify what I have given your suggestion, but I get an Object variable or With variable not set error  when I start to try to loop through the pivot tables...


Sub GetCurrentDataFile()

   Dim fso As Object, fld As Object, fil As Object
    Dim TestDate As Date
    Dim MostRecentCreatedFile As String
    Dim FolderPath As String
    Dim FullString As String
     Dim sh As Worksheet, qy As QueryTable
     Dim pt As PivotTable, pc As PivotCache
     Dim OldPath As String, NewPath As String
     Dim rng As Range
     Dim ws As Worksheet

     
    '  Sample path statement
    '   \\nc-files.mynatc.us\PSREPORT\[EXPORT_BUDGET-317995.xls]sheet1'!$A$3:$P$2213
    
    FolderPath = "\\nc-files.mynatc.us\PSREPORT\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FolderPath)
    
    For Each fil In fld.Files
        If fil.DateCreated > TestDate Then
            TestDate = fil.DateCreated
            MostRecentCreatedFile = fil.Name
        End If
    Next

    FullString = FolderPath & "[" & MostRecentCreatedFile & "]sheet1'!$A$3:$P$4000"
    Workbooks.Open FolderPath & MostRecentCreatedFile

'    Set fil = Nothing
'    Set fld = Nothing
'    Set fso = Nothing

     OldPath = FolderPath
     NewPath = FullString

    'Go to the workbook that has all the pivot tables in it
    
    Workbooks("Budget Report Pivot.xlsm").Activate
    ActiveWorkbook.Worksheets("Admin Update").Range("a6").Select

'Error happens here

       For Each pt In ws.PivotTables

            pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
            On Error Resume Next
            pt.PivotCache.CommandText = StringToArray(Application.Substitute(pt.PivotCache.CommandText, OldPath, NewPath))
            If Err.Number <> 0 Then
               Err.Clear
                On Error GoTo 0
                Application.ScreenUpdating = False
                Set rng = pt.TableRange2
                pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1).Range("A1")
                ActiveCell.PivotTable.PivotCache.CommandText = StringToArray(Application.Substitute(pt.PivotCache.CommandText, OldPath, NewPath))
                ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2
                ActiveWorkbook.Close False
                Set pt = rng.PivotTable
                Application.ScreenUpdating = True
            End If
            pt.PivotCache.Refresh
        Next pt
    
ErrHandler:
MsgBox Err.Number & ":" & Err.Description


End Sub

Open in new window

0
dlmilleCommented:
that's because you don't have ws defined.

it should be:

for each ws in activeworkbook.sheets
..
..

  for each pt in ws.pivottables

etc....

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dlmilleCommented:
If you go back to the original link, you'll want to keep the key elements of the code, elimininating the query-related code (though it doesn't hurt to keep it because if you don't have queries, that will just fall through and if you do and they are folder change related, it will change them correctly, as well).

Cheers,

Dave
0
BEBaldaufAuthor Commented:
Ok, tried going back to the original link as suggested.
I get a runtime error the first time it his "pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, oldPath, NewPath)

Public Sub CurrentBudgetFile()


   Dim fso As Object, fld As Object, fil As Object
    Dim TestDate As Date
    Dim MostRecentCreatedFile As String
    Dim FolderPath As String
    Dim FullString As String
     Dim sh As Worksheet, qy As QueryTable
     Dim pt As PivotTable, pc As PivotCache
     Dim OldPath As String, NewPath As String
     Dim rng As Range

    FolderPath = "\\nc-files.mynatc.us\PSREPORT\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FolderPath)
    
    For Each fil In fld.Files
        If fil.DateCreated > TestDate Then
            TestDate = fil.DateCreated
            MostRecentCreatedFile = fil.Name
        End If
    Next
    
    FullString = FolderPath & "[" & MostRecentCreatedFile & "]sheet1'!$A$3:$P$4000"

    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing


     OldPath = FolderPath
     NewPath = FullString

     For Each ws In ActiveWorkbook.Sheets
       For Each qy In ws.QueryTables
            qy.Connection = _
                  Application.Substitute(qy.Connection, _
                  OldPath, NewPath)
            qy.CommandText = _
                  StringToArray(Application.Substitute(qy.CommandText, _
                  OldPath, NewPath))
            qy.Refresh
       Next qy

       For Each pt In ws.PivotTables
            pt.PivotCache.Connection = _
                  Application.Substitute(pt.PivotCache.Connection, _
                  OldPath, NewPath)
            On Error Resume Next
            pt.PivotCache.CommandText = _
                  StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
                  OldPath, NewPath))
            If Err.Number <> 0 Then
                Err.Clear
                On Error GoTo 0
                Application.ScreenUpdating = False
                Set rng = pt.TableRange2
                pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
                           .Range("A1")
                ActiveCell.PivotTable.PivotCache.CommandText = _
                           StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
                           OldPath, NewPath))
                ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2
                ActiveWorkbook.Close False
                Set pt = rng.PivotTable
                Application.ScreenUpdating = True
            End If
            pt.PivotCache.Refresh
        Next pt
Next ws
End Sub

Open in new window

0
dlmilleCommented:
For some reason, you elmininated the declaration of ws as Worksheet (not a biggie).

On your runtime error - you need to ensure that oldpath and newpath exist, and are of the form:

     OldPath =  "C:\OldPath\Folder"
     NewPath = "C:\NewPath\Folder"

It appears to me you've added a "\" to the end of your pathnames.

I suggest you only work with the MSFT code for now - hard code your old and new path right where MSFT suggests in their code, then test to see if there are errors, as we could be having problems with your ancillary coding which is not quite efficient for either of us.

Cheers,

Dave
0
BEBaldaufAuthor Commented:
Hard coded paths as suggested:
     OldPath = "\\nc-files.mynatc.us\PSREPORT\[EXPORT_BUDGET-317853.xls]sheet1'!$A$3:$P$2202"
     NewPath = "\\nc-files.mynatc.us\PSREPORT\[EXPORT_BUDGET-318074.xls]sheet1'!$A$3:$P$2213"
Runtime Error occurs at same place as before...
0
dlmilleCommented:
Perhaps I didn't understand your original post as the code I gave is for changing from one folder to another (or one drive\path to another drive\path).

You are also changing the source range, in addition to different filename.

1.  For change of filename, I would think just updating links should do that.  Please try changing the link of the source file and advise after pivot refresh if that works.

Let me do a bit of research and get back to you.

Dave
0
BEBaldaufAuthor Commented:
I am trying to change the source to another file (specifically whichever file is most recent), which will be located within the same folder.
(The number of rows may change, but I planned to just use a number larger than I expect to need.)
I can change all the source links manually (not with code), but only if I open the source files first.
0
dlmilleCommented:
ok - the problem becomes much simpler.  Just trying to change the file name and potentially the address range, right?

Try this code - I just built a test case and was able to change filename/source range and worked great.

Sorry for my confusion!

 
Sub changePivotSource()
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Dim myNewSource As String
    Set wb = ActiveWorkbook
    myNewSource = ThisWorkbook.Path & "\[Portfolio Workbook 1.xlsx]Data!$A$22:$AS$1021" '<- put your new path and addressing here
    For Each ws In wb.Worksheets
    
        For Each pt In ws.PivotTables
            pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=myNewSource)
        Next pt
    Next ws
    
End Sub

Open in new window


Dave
0
BEBaldaufAuthor Commented:
I get a runtime error, invalid procedure call on line 11 of your code
0
dlmilleCommented:
Ok :)

What version of Excel are you running?

Dave
0
BEBaldaufAuthor Commented:
2010
0
dlmilleCommented:
I've just tested in Excel 2010 and it works just fine, as well - at least with my test pivot tables.

Perhaps try modifying line 11 to:

pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=myNewSource,Version:=xlPivotTableVersion14)

I'm not sure that will really make a difference, however.

Can you advise if it updated ANY of the pivot tables with the source change?  Perhaps just try on one to narrow the combinations?

Thanks,

Dave
0
dlmilleCommented:
By the way, I noticed the latest code I provided is not substantially different from your original code.  When I try your approach on one pivottable, I get similar correct results.

I'm a bit puzzled by the invalid procedure call you get, as I don't get that.

Please download this file, create a pivot table in it sourcing one data source, then make the VBA changes to get to the other datasource and advise if it works.  This is to isolate the problem.  You'll delete the existing pivot table on the sheet, and add a new one sourcing a data source.  Then in code, change it and test.

Dave
testPivotChangeSource-r1.xls
0
BEBaldaufAuthor Commented:
Using your test file, I created a pivot table from a source file, edited the code to point to a different source file, and ran it.

The run-time error I get is '-2147024809 (80070057)': Cannot open PivotTable source file
0
dlmilleCommented:
Ok here are three files.  Put them all in a trusted location, same directory.  Run the macro to see it refresh.  Comment out the myNewSource line and uncomment the other one which will make it look at the other file and a slightly different range.  Run the macro again.

Does all that work?  If it does, at least we're on the same page and I can work other dimensions of diagnosis, if not, we start with that.

You may have gotten an error, if you didn't explictly define the source path, SHEET NAME, and / or RANGE correctly.

Here are the three files attached.

Sorry I didn't get back sooner.  For some reason Hotmail went on the blitz and I wasn't notified of updates you sent this morning.

Dave
testPivotChangeSource-r1.xls
testwkb1.xls
testwkb2.xls
0
BEBaldaufAuthor Commented:
Thanks for getting back to me, I'm sorry I'm not much help figuring this out.
The three files you supplied worked great.  Source file got changed without a problem.
I'm about ready to give up on this, but here are the strings I'm using:

This is the current source of the pivot table (copied directly out of the Change Data Source window):
'\\nc-files.mynatc.us\PSREPORT\[EXPORT_BUDGET-318059.xls]sheet1'!$A$3:$P$2187

And, taken from the Watch window in VB, the string identifying the new path is:
"\\nc-files.mynatc.us\PSREPORT\[EXPORT_BUDGET-318120.xls]sheet1'!$A$3:$P$3000"
0
dlmilleCommented:
ok - let's try something a little obvious.  Try to run it changing the source to exactly what the source currently is.  Does that work?
0
dlmilleCommented:
I'm not quite ready to give up, either.  However, this might be an alternative (to keep in the back pocket):

This doesn't deal with the changing range issue, but might be a satisfactory go-forward solution.

1.  Update the pivot source data such that the range is large - e.g., A3:P3000 - and thus it will stay that way
2.  When you get a new file, rename it to the source file name being referenced by your pivot table
3.  When you open the workbook with the pivot table, just doing a refresh should display the new data.

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BEBaldaufAuthor Commented:
Hi Dave,

Well, this confirms that something somewhere just is not working... I did try renaming the source to exactly what the source is currently.  SAME ERROR.  ACK!  (Sorry, a bit frustrated.)  I just won't get it.

Well, I so appreciate your time and effort with this.  I did like your last suggestion, and may very well use it!

So can I award you the answer points despite our inability to pinpoint the problem and find an answer?  

Thanks again, truly!
Bethyn
0
dlmilleCommented:
Its not THAT critical, but you can award if I demonstrated that its possible with the test files I posted, or if you believe the alternative I proposed is acceptable.  

I did post in another forum to see if other Experts might chime in, but none have, so far.

PS - when you say it didn't work - what error did you get?
0
dlmilleCommented:
If it is possible to upload a sample of your datafile and your driving workbook, perhaps I can look at your exact code and how it interacts? (assuming non-confidential, of course).

If not, one more thing to try - put your source files on a local folder and modify your code to that path and see if it works.  Not sure, but we should take the network location out of the equation.

Dave
0
dlmilleCommented:
Another thing:

copy your pivot table driving workbook into the \\nc-files.mynatc.us\PSREPORT\ directory

then try:

myNewSource = thisworkbook.path & "\[EXPORT_BUDGET-318059.xls]whatever:whatever"

Just in case there's a type-o in your path that you've hardcoded?

Dave
0
BEBaldaufAuthor Commented:
(I still receive that runtime error.)

Thanks for the assistannce, and if you ever find a solution, please give me a holler!!!

Bethyn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.