Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,

    Recently the finance department has upgraded to Excel 2007 from 2003. The rest of the company is still on 2003. We have some pretty large databases that our pivot tables read. Here is the problem:

    We have a large database saved in an Excel 2007 file (.xlsm)
    The pivot table that reads this Excel 2007 database is located in an Excel 2003 file.
    The pivot table range is A1:P1048576, which then reverts automatically to $A:$P (no numbers appear).

    The next time we re-open the 2003 file, the range in the pivot table cuts itself off at 65536 (as this is 2003's row limitation).

    Is there a way around this to make it stay at the larger 2007 row limitiation of 1048576 as opposed to it reverting back to the 2003 row limitation of 65536?

    I've recorded a macro to change the range automatically to the 1048576 and it doesn't like it... likewise when I change the macro to read as $A:$P (as it appears when I fix the pivot table with the 1048576), the recorded macro reads as C1:C16???

    Any ideas on a work around until the rest of our company is on 2007 is much appreciated!

    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd store the data in an Access database. An Excel pivot table can be based directly on an Access table; the end users don't need to have Access installed to use it.
    It would need to be a database in Access 2000 or 2002-2003 format (.mdb), for Excel 2003 can't read a database in Access 2007 format (.accdb).

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    We have used Access in the past, however we use msquery to download it into Excel, then we use macros to manipulate the data. Likewise, we have formulas in the database as well. In addition, we don't have Access on any of our desktops anymore. Do you have any ideas on why the macro to change the range is not working? When we manually "fix" the range in the pivot table everything works fine. Below is the recorded macro for when the database (in 2007) is open and I manually fix the range. It's wierd that it says C1:C16 as the real range is A1:P1048546???


    Sub Macro8()
    ActiveSheet.PivotTables("PivotTable1").ChangePivot Cache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "K:\Groups\Payroll\2008 GL Payroll\Payroll Uploads\Testing\[Trial Balances-NEW.xlsm]data!C1:C16" _
    , Version:=xlPivotTableVersion10)
    End Sub


    Thanks!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Users don't need to have Access installed to be able to use a pivot table based on a table in an Access database.

    I don't have Office 2007, so I don't know why the recorded macro is incorrect. You can edit the range in the macro, hopefully it will run OK then.

    An alternative would be to save the workbook with the source data in Excel 97-2003 format. There should be no compatibility problems then, but of course the source data would be limited to a maximum of 65,536 rows.

    (It seems strange to me that a Finance department would switch to a new version of Excel before things have been thoroughly tested, but who am I...)

Posting Permissions

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