Yep, it’s true.  It appears that one hand of Microsoft doesn’t know what the other is doing.  I haven’t found a lot of hits on this but apparently both sides of this bug confirm its existence and both claim it’s not their problem.

Symptom:

• Microsoft Excel cannot find the references inside a pivot table that references the same workbook when opened from Internet Explorer

Cause:

• Internet Explorer adds ‘[X]’ to the end of the file name when storing the file in the IE temp folder, where X represents an incrementing integer based on the number of downloads of the same file, and the square brackets are illegal characters for file names in Windows

Steps to reproduce:

• Browsing with Internet Explorer
• Clicking on a link to an Excel spreadsheet with a pivot table in it that references itself
• Clicking on "Open" rather than "Save"

The only solution I could think of was to write a VBA macro to rename the file and reattach all of the pivot tables.  I hope this saves someone else some trouble in the future.  When you think of me, think of me fondly…

( You’ll have to add a reference to Microsoft VBScript Regular Expressions 5.5 or better. )

 Sub Auto_Open()
    
    Dim sFilename As String   ‘ the corrected filename
    Dim oASheet As Worksheet  ‘ the worksheet object
    Dim oPT As PivotTable     ‘ the pivot table object
    Dim sTempSource As String ‘ the pivot table temporary source variable
    Dim sNewRange As String   ‘ the new pivot table source variable
    Dim reg As New RegExp     ‘ a regular expression for matching wildcards
    Dim i As Integer          ‘ looping integer
    Dim j As Integer          ‘ looping integer
    
    ‘ Set a regex to test whether this is a temp spreadsheet from IE.
    ‘ Only a temp spreadsheet from IE will have square brackets in the filename.
    reg.Pattern = "[[\]]"
    
    If reg.Test(ActiveWorkbook.FullName) Then
        ‘ the temp filename has square brackets in it
        
        ‘ just a little message telling the user what’s going on
        MsgBox "Renaming temp file and reattaching pivot tables…"
        
        ‘ The .Name property already has the brackets stripped out of it so
        ‘ we can build the new path using it and the .Path property.
        sFilename = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
        ‘ Now we save the file so that the assignment of source data to the
        ‘ pivot tables won’t fail.
        ActiveWorkbook.SaveAs Filename:=sFilename
            
        ‘ The range for the pivot table requires a square bracket
        ‘ just before the file name so we rebuild the path, less the drive letter.
        sNewRange = Replace(ActiveWorkbook.Path, "C:", "") & "\[" & ActiveWorkbook.Name & "]"
        
        ‘ now set the regex for the tail end of the spreadsheet, including the trailing "]"
        reg.IgnoreCase = True
        reg.Pattern = ".*\.xls\]"
        
        ‘ now loop through all of the sheets looking for pivot tables
        For j = 1 To ActiveWorkbook.Worksheets.Count
            Set oASheet = ActiveWorkbook.Worksheets(j)
        
            ‘ now loop through any pivot tables on the worksheet
            For i = 1 To oASheet.PivotTables.Count
                Set oPT = oASheet.PivotTables(i)
            
                sTempSource = oPT.SourceData
                
                ‘ go ahead and replace the invalid path with the new path
                sTempSource = "’" & reg.Replace(sTempSource, sNewRange)
            
                ‘oPT.PivotTableWizard SourceType:=xlDatabase, SourceData:=sTempSource
                
                ‘ assign the new path to the sourcedata property
                oPT.SourceData = sTempSource
                
                ‘ release the object
                Set oPT = Nothing
            Next i
            
            ‘ release the object
            Set oASheet = Nothing
        Next j
        
        ‘ Save the workbook, just to be thorough.
        ‘ Without saving a user would be able to close the workbook without saving
        ‘ and then open the workbook from the
        ‘ recent documents list and have broken pivot tables.
        ActiveWorkbook.Save
    Else
        ‘ Not a spreadsheet with square brackets in the filename
        ‘MsgBox "No renaming required"
    End If

End Sub