Excel 2003 Pivot Table using Excel 2007 database

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

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”).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“K:GroupsPayroll2008 GL PayrollPayroll UploadsTesting[Trial Balances-NEW.xlsm]data!C1:C16” _
, Version:=xlPivotTableVersion10)
End Sub

Thanks!
Lana

Reply | Quote

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…)

Reply | Quote