Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refreshing multiple tables w/ external source data (2002)

    Currently, I have a spreadsheet that has 10 pivot tables that link to an outside database but use the same pivot table cache. Each month, the database name is changed to incorporate the current month (example: 0508 Database for May). I would like to create a command button to change the source data to the appropriate month and refresh all of the pivot tables. I have the following code to refresh multiple pivot tables with data in the same workbook. How do I modify this code in order to change the source data for each pivot table to the current month?

    Private Sub CommandButton1_Click()
    Sheets("Sheet1").Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    Sheets("Sheet2").Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    End Sub

    Any help that you can provide would be appreciated.
    Thanks,
    alynnew

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Refreshing multiple tables w/ external source

    I think you need to look at the Connection property of the PivotCache object. The database name should appear within that and you can change it there easily if you only have one pivot table based off it. In my experience, if there are multiple tables based off the same cache, it can be very difficult to simply change the connection without having to delete and recreate each pivot table. Can you tell us what the connection string is?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    Welcome to Woody's Lounge!

    If the name of the table or query within the database remains the same, you can probably use code like this:

    Dim strPath As String
    Dim strFile As String
    ' Modify as needed
    strPath = "Catabases"
    strFile = "0508.mdb"
    With ActiveWorkbook.PivotCaches(1)
    .SourceConnectionFile = strPath & strFile
    .Refresh
    End With

  4. #4
    New Lounger
    Join Date
    Jun 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    Sorry for the confusion. Inventory transactions are downloaded from our AS400 system into the "database" file, which is actually an excel file rather than an access database. The information is downloaded into the "data" tab of the workbook. Does the referenced code only apply to access databases? Using the code, I receive error message 1004 - application defined or object defined error. Is there a way to modify the code accordingly?

    Thanks for all of your help,
    alynnew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    In that case, a macro with just one instruction

    ActiveWorkbook.PivotCaches(1).Refresh

    should be sufficient to update all pivot tables.

  6. #6
    New Lounger
    Join Date
    Jun 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    Before the pivot tables refresh, is there anyway to change the excel spreadsheet in the source data? Each month I need the file name to change as follows:
    KGroupsFinance2008CostingDownload Files0508 All Database
    KGroupsFinance2008CostingDownload Files0608 All Database

    Thanks,
    alynnew

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    I'm getting lost. Are the pivot tables based on data imported into the workbook itself, or are they based on an external workbook?

  8. #8
    New Lounger
    Join Date
    Jun 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    The pivot tables are based off an external workbook. The name of the external workbook changes each month based on the month of the file.

    Thanks,
    alynnew

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    Try a macro like this:

    Sub ReplaceSource()
    With ActiveWorkbook.PivotCaches(1)
    .SourceData = Replace(.SourceData, "0508 All Database", "0608 All Database")
    .Refresh
    End With
    End Sub

    Of course, you can replace the literal names with expressions that concatenate the correct names.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing multiple tables w/ external source data (2002)

    If the data is based on an sql statement, then the filename and path might be included there as well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •