Pivot Table - Compatibility Mode Message

[jim may]

I run xl2007 ONLY in my office. A client needed some additional work done on a file (with Macros) created solely in xl2003 (abc.xls). They e-mailed me the file and I performed the changes. The file when opened by me (in xl2007) read Compatibility Mode in the FileName header all the time I was working with it.
When I saved the file (as File type Excel 2003 wb) - (r-abc-xls) the compatibility-checker came up indicating:
The PT in this wb is built in the current file format and will not work in earlier versions of Excel. Only PT's that are created in Compatibility Mode will work in earlier versions of Excel.
I clicked "Continue" and Saved as .xls (2003 file type).

At the office, they loaded "r-abc-xls" and ran the single macro (invovling the only PT) and they got a R/T 1004 message:

This PT was created in a later version of Excel and cannot be updated in this version. To update it, click OK and then open the wb in the version of Excel it was originally created in. grrrr

Any clues as to the direction to go?

Thanks in advance,

Jim

 

[Andrew Poulsom]

Did you refresh the pivot table? According to this that upgrades it:

http://office.microsoft.com/en-us/e...able-formats-in-office-excel-HA010167298.aspx

 

[RoryA]

That's not my understanding (or experience). If you open a 97-2003 file with a pivot table and refresh but save to 97-2003 format, it's still fine. It's only if you refresh then save as 2007 format file that it updates.
If you create a PT through the Excel user interface in a 97-2003 format file in 2007 it should work fine in 2003 and earlier. However, if you create it in code and specify a later version, that would be an issue.

 

[jim may]

The PT in this wb is built in the current file format and will not work in earlier versions of Excel.

Seeing that "Only PT's that are created in Compatibility Mode will work in earlier versions of Excel"..

I opened the abc.xls fresh using xl2007, enabled-macros, and verified that I was in Compatibility Mode. I then went to my data source table, created a New PT then Deleted the Old PT. Saved as FileType Workbook 97-2003 (.xls). The Compatibility-Checker comes up saying, again

The PT in this wb is built in the current file format and will not work in earlier versions of Excel

Earlier there was also a mention (in the Comp-Checker) that the PT STYLES assigned while updating wasn't compatible, so I changed the PT Style to NONE.

The next time I saved the file the "Styles" issue disappeared...

Something is really throwing up a ROAD-BLOCK

In a standard module the Code Crashed at the Line in RED...
...
Sheets("PTReport").Activate
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Here's the code 9below) in the PTReport sheet code window..

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim NumMonthDays As Long
Application.EnableEvents = False
NumMonthDays = Sheets("Start Here").Range("C27").Value   'can be either 28,29,30 or 31
Select Case Sheets("Start Here").Range("C23").Value     'between 1 to 7
    Case Is = 1
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/7)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 2
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/14)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 3
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/21)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 4
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/28)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 5
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/29)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 6
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/30)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Is = 7
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-EstConvPerDay").Formula = "(Samt/31)"
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("TM-ConvUp").Formula = "'TM-EstConvPerDay'* " & NumMonthDays
    Case Else
    MsgBox "Select Period of this report on Sheet Start Here - Cell C23"

End Select
Application.EnableEvents = True
End Sub

So again, the message being produced - "Only PT's that are created in Compatibility Mode will work in earlier versions of Excel" is not helping me...

Any additional thoughts??????

Jim

 

[Andrew Poulsom]

The behaviour you describe doesn't fit with:

http://blogs.office.com/b/microsoft-excel/archive/2006/08/30/pivottable-compatibility.aspx

That said I have seen your problem reported elswhere (but no solution):

http://answers.microsoft.com/en-us/...-save-in/4d1a8d49-66eb-4ab0-a3fd-33e58520582e

 

[RoryA]

Are you creating the pivot table manually?

 

[jim may]

Did you refresh the pivot table? According to this that upgrades it:

http://office.microsoft.com/en-us/e...able-formats-in-office-excel-HA010167298.aspx

While at the Client's Office (They are exclusively 2003 (Excel)) - I open the file r-abc.xls and enable-macros then immediately before doing anything else I go to Sheet "PTReport" click inside the PT - then at the Menu, goto Data (to Refresh), yet both the Main 1) Pivot Table and Pivot Chart.. and 2) Refresh options in the drop-down ARE GREYED-OUT - a CLUE that something is wrong already...

Totally puzzled here... (I'm beginning to read the reference material you provided) - Thanks Again...

Jim

 

[jim may]

Are you creating the pivot table manually?

No.

The only way I know how to create a PT is by using the "Step-Through (Wizard)" approach.

Jim

 

[jim may]

The behaviour you describe doesn't fit with:

http://blogs.office.com/b/microsoft-excel/archive/2006/08/30/pivottable-compatibility.aspx

That said I have seen your problem reported elswhere (but no solution):

http://answers.microsoft.com/en-us/...-save-in/4d1a8d49-66eb-4ab0-a3fd-33e58520582e

Andrew, This is very encouraging (except for the "but no Solution" - LOL, but
I've tried 3 or 4 times already to access the related Site (underneath), but keep getting from the Site:

Thread Not Found

Is there another link to same? - I'd love to read its content..

Tks Again,

Jim

 

[RoryA]

The only way I know how to create a PT is by using the "Step-Through (Wizard)" approach.

That's what I mean by manually - i.e. not in code.

Do you change any settings in the pivot table when you create it? Do you have Service Pack 2 installed?