Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

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

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!
0
heyday2004
Asked:
heyday2004
  • 5
  • 3
4 Solutions
 
wellousCommented:
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,
Brgds
Wellous
0
 
heyday2004Author Commented:
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.
0
 
wellousCommented:
Heyday,

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

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.
        ActiveWorkbook.Save
    Else
        ' 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
Brgds
Wellous
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
heyday2004Author Commented:
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!
0
 
wellousCommented:
Hi,

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.

Brgds
Wellous
0
 
heyday2004Author Commented:
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.
0
 
wellousCommented:
Heyday,

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.

GL,
Wellous
0
 
wellousCommented:
thanks for the grade :)
Wellous
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now