none
VBA - Connecting pivot tables to another workbook RRS feed

  • Question

  • Hi,

    I have been looking around many forums but cannot seem to find any answer or hints for my problem.

    I need to use VBA to link some Pivot tables that are in Workbook A to data that is in another Workbook. Due to the size of the Data I need to keep those two workbooks distinct (I cannot copy the data in Workbook A).

    I have a new Workbook with data every day and need to update the Pivot tables link to this new Workbook to refresh them. I have tried to Record a Macro and it gives me a code with the path to the Workbook (for example, let’s say today I have Workbook B, tomorrow will be Workbook C, etc.) and the sheet where the data is located:

    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "[Workbook B.xlsx]Sheet1!R1C1:R40000C12" _
            , Version:=xlPivotTableVersion14)

    But when I try to run this code from VBA, it gives me a run-time error message:

     ‘-2147024809 (80070057)’: Cannot open PivotTable source file ‘C:\users\...\[Workbook B.xlsx]Sheet1!R1C1:R40000C12’

    I have tried modifying the path several times but the error keeps coming up, would it be because it is connecting to another workbook?

    Appreciate any help,

    Thanks,

    Greg

    Sunday, December 8, 2013 1:49 AM

All replies

  • I assume you have a workbook which is same and pivot table is located here.

    And this pivottable refers to data of another workbook which changes every day.

    Confirm pls.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, December 9, 2013 2:51 PM
    Answerer
  • If above is TRUE following will do the job...

    Assumptions:

    data resides at Master!a1:c400

    Note:

    Some modification may be needed on exact requirement of your situation.

    It allows you to choose any excel 2007 file.

    Sub upatePIVOt()
    
        Dim PC As PivotCache
        Dim PT As PivotTable
        Dim CON As WorkbookConnection
        Dim stCon As String
        Dim spath As String
        Dim sCommandText As String
        
        
        spath = Application.GetOpenFilename
        
        stCon = "oledb;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & spath & ";" _
         & "Extended Properties=Excel 12.0 Macro;"
        
        sCommandText = "select * from [Master$a1:c400]"
        
        On Error Resume Next
         ThisWorkbook.Connections("MyConnection").Delete
        On Error GoTo 0
        
        Set CON = ThisWorkbook.Connections.Add(Name:="MyConnection", Description:="MyConnection", connectionstring:=stCon, CommandText:=sCommandText, lcmdtype:=xlCmdSql)
        
        Set PC = ActiveWorkbook.PivotCaches.Create(xlExternal, SourceData:=CON)
        
        Sheets.Add
        Set PT = PC.CreatePivotTable(TableDestination:=ActiveSheet.Name & "!R1c1", _
          TableName:=ActiveSheet.Name, DefaultVersion:=xlPivotTableVersion12)
                             
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, December 10, 2013 8:44 AM
    Answerer