Pivot Table Error

[Anthony G.]

Hello to All…

Upon opening an existing file, I receive the following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems.

I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error:

ActiveSheet.Unprotect Password:="test"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("K9").Select
Selection.Copy
Range("K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("h161").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("I:I").Select
Selection.ColumnWidth = 14.86
Range("A19").Select
ActiveSheet.Protect Password:="test"
End Sub

I guess I understand why I’m getting the Run time error (because the PT no longer exists); question is, why is it being discarded in the first place…?!?

Any assistance in correcting this will be greatly appreciated.

Thank you…Anthony

 

[dsgs]

hi all,

i've got the same problem (integrity problems) but i'm running with office 2003 (on the network only office 2003), on my pc office 2003 SP2)

any ideas what causes my problems????

thx!!!

 

[genejocky]

Hi gang,

I've just found myself with the same problem.
The excel file was on the company network run on Office XP with service pack 3 installed.
Microsoft claims that SP3 fixes the problem. I guess not

Anone have an idea of a way forward?
Office 2003 rollout is apparently a very long way off.

 

[hidden]

Since there is already an existing topic about this. I will just post here

What I wanna do is to have an Excel file that has a button in it. When you click the button it will ask for the filename that will serve as the source of the Pivot table that i wanna generate. I recorded a macro, unfortunately it gives error 400. Here is my code:

Sub OpenFile()
    Dim vFilename As Variant
    vFilename = Application.GetOpenFilename("Excel Files,*.xls")
    If vFilename = False Then
        Exit Sub
    Else
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "[" & vFilename & "]InpuData!R1C1:R50000C5").CreatePivotTable TableDestination:= _
        "", TableName:="Project Name Hour", DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveSheet.PivotTables("Project Name Hour").PrintTitles = True
        ActiveSheet.PivotTables("Project Name Hour").AddFields RowFields:=Array( _
            "Code", "Mac_Num", "Name"), ColumnFields:="Month"
        ActiveSheet.PivotTables("Project Name Hour").PivotFields("Hour").Orientation = _
        xlDataField
        ActiveWorkbook.ShowPivotTableFieldList = False
    End If
    
End Sub

If you have any idea please help me. Thanks.

 

[hidden]

anyone who can help me?

 

[greggy8]

Hi,

Yeah this is a real pain. I work for an organisation that would not apply SP3. The way I got around this problem was to remove all passwords, then use a macro to set the protection on and off at the time the Excel workbook opens. I also disabled the Protection menu, to prevent users from manually playing with the protection settings. Not perfect, but achieved what i wanted, which was a robust template. Basic code I used attached:

Sub DISABLE_PROTECTION_FUNCTION()
On Error Resume Next

Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=30029)
oCtrl.Enabled = False 'Set To True To Re-enable
Next oCtrl
End Sub


Sub Protect_Worksheet(PWorksheetName As String)
'No Passwords are set, because it can cause Pivottable Corruption - see Microsoft bug

ActiveWorkbook.Protect

If (PWorksheetName = "") Then

Sheets("Receipts").Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

Sheets("Payments").Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

Sheets("BankStmt").Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

Sheets("Instructions").Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

Else

Sheets(PWorksheetName).Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

End If

End Sub



Sub Unprotect_Worksheet(PWorksheetName As String)

ActiveWorkbook.Unprotect

If (PWorksheetName = "") Then
Sheets("Receipts").Unprotect
Sheets("Payments").Unprotect
Sheets("BankStatementPDF").Unprotect
Sheets("Instructions").Unprotect
Else
Sheets(PWorksheetName).Unprotect Password:="trust"
End If

End Sub