pivot table refresh not working in IE, but working in Firefox.

Posted on 2011-04-29
Last Modified: 2012-05-11
When I download an excel file with Pivot table from the html link in IE, I have data refresh issue (or Reference is not Valid issue). But I don't have problem in Firefox:

When I clicked the download, the browser will ask me "do you want to open or save this file", I choose open it directly, and the file is downloaded to the temp folder, and then I see the pivot table is blank. The refreshing doesn't work (I've selected the Pivot table option: Refresh data when opening the file).

But in Firefox, when I open it, the Pivot table is refreshed and everything is ok.

And even for IE, when I choose save as option to save it to desktop, then open it, everything is good too.

The only thing is when I open an excel file in IE, I will have refreshing not working issue for Pivot Table (no data shown in IE pages)

Any hint is appreciated. Thanks!
Question by:heyday2004
    LVL 5

    Accepted Solution

    Hi Heyday,

    Your problem is related to the Add-Ons on your IE browser

    Your pivot tables are not downloaded correctly because it seems that you don’t have MICROSOFT OFFICE CONTROL activeX control , and probably you don’t use an updated version of IE.

    To check for this , open the web site which contains the excel sheet with pivot table...
    Then go to IE browser ( Tools > Manage add-ons ) then check for the activeX name:MS office control.

    If you find it then we have to dig deeper , otherwise please check Microsoft support site to download it depend on your browser version.

    Hope it helps,

    Author Comment

    I did have this on the IE browser.

    Here is what I found but I dont know why:
    In IE, the file opened is saved in cache with some [] on the name, say, it could be saved as:
    document[1].xls And this causes problem. I could repeat this in my local desktop:
    1. for the same file with Pivot table: document.xls, when I renamed it with docuemtn[1].xls, when I refresh it, I will get errors (reference is not valid).But when I revised the name by removing [] sign, say, document.xls, I don't have any issue in refresh the pivot table data source. In Firefox, I guess the files in the cache don't have []?

    Thanks for any further hint.
    LVL 5

    Assisted Solution


    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.

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

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

    Use the below code in your save worksheet even if has () it still can work ..

    ( 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.
            ' Not a spreadsheet with square brackets in the filename
            'MsgBox "No renaming required"
        End If
    End Sub

    Tell me if you get stuck.
    Good luck

    Author Comment

    Thanks, here is what I did (i'm new to Macro):
    i pressed alt+F11 and entered the above content and saved the excel file. and when I opened the excel file, I got a warning: Macro is disabled, when I enabled it, I still got the reference is not valid error. How did you revise it to make it work? Thanks!
    LVL 5

    Assisted Solution


    No please create a Module first then paste the above code into it.
    Press Alt+ F11 > then right click on the left menu , where is sheet1 sheet2...etc.
    and select Insert > Module..

    Then paste the code, save and go to tools > References > and search for (((Mircosoft VBscript Regular Expressions 5.5 ))))) or better
    Then go back to your excel sheet and press Alt+F8 and run the macro :)

    Good luck.


    Author Comment

    Thanks a lot. Seems it requires users to enable Macros? I might have permission problem to enalbe Macro? Any other solutions that don't require security setting changes? Thanks.
    LVL 5

    Assisted Solution


    In your comments dated (((05/01/11 10:47 PM, ID: 35502871))))
    You said :
    [[[[[[[[when I opened the excel file, I got a warning: Macro is disabled, when I enabled it, I still ...]]]]]]]]
    So you had the permission to enable tthe macro first time, pls enable it again and check to give me the result.

    LVL 5

    Expert Comment

    thanks for the grade :)

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now