?
Solved

Why adding a "[]" on my excel file name causes issue in Pivot table refresh?

Posted on 2011-05-01
24
Medium Priority
?
2,119 Views
Last Modified: 2012-05-11


I have an excel file with normal pivot talbe: document.xls,

when I renamed it with docuemtn[1].xls, and then refresh the data source, I will get errors (reference is not valid, etc.). But when I revised the name by removing [] sign, say, document.xls, I don't have this issue anymore in refresh the pivot table data source.

The only difference is the "[]" sign in the excel document name.

Anyone could give me some hint? Thanks a lot!
0
Comment
Question by:heyday2004
  • 10
  • 6
  • 5
  • +2
24 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 224 total points
ID: 35501646
Square brackets are not valid in file names referenced from Excel. They cannot contain colons (:), question marks (?), asterisks (*), forward slashes (/), backward slashes (\), left square brackets ([), right square brackets (]).

Kevin
0
 

Author Comment

by:heyday2004
ID: 35502282
But when I open an excel file from web, IE will try to open it in cache with a temp name with "[ ]" sign in it. And as long as I don't refresh the pivot table, everything looks good. I could also rename the file with square brackets without problem and could open it without problem as long as I don't refresh the pivot table. Thanks for any further hint on this naming issue and where could I find more information on square brackets in excel!
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35502325
I'm a bit confused.  Square brackets appear in all my workbook references to other files, lol..

Here's one example I just created:

[A1]=[File2.xlsx]Sheet1'!$B:$B

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35502351
can you post an example that links to a (supposedly public?) website, for understanding?

Dave
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35502356
If not, in both the working and non-working versions, can you go to change data source and copy what's there to share?

Dave
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 556 total points
ID: 35502389
Dave, the [] brackets in a file reference like [A1]=[File2.xlsx]Sheet1'!$B:$B are not part of the actual file name. The problem arises when the file name itself contains [] signs. Zorvek's answer stands.
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35502442
@teylyn - I sort of gathered that (I assumed it was a reference like I shared, not in a filename despite what was written) and believe it could be a communication issue because the asker could likely be seeing a similar reference.  That's why I asked to see an example of the source data reference.

@heyday - how about some print screens to show us what you're looking at?

Dave
0
 

Author Comment

by:heyday2004
ID: 35502572
Thanks for the replies. dlmille: I dont have the file at hand but you could repeat this in any of your excel file with pivot table (with hide or unhide data tab) when you rename it with sqaure bracket in the file name. What I meant, as zorvek and teylyn said, is when the real physical file name contains square brackets (we will see this kind of temp files in internet cache files, etc.), there will be issue when you choose "pivot table tools"--> refresh all. The error message is like: reference is not valid, etc. I know now square brackets is not legal in excel file name, but why I could still save it with square brackets and open it without problem (as long as i dont refresh the pivot tables). Thanks for any further hint.
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35502631
If you replicate, do some alt-printscreens and post the image here.  Will try to assist if I can replicate what you're seeing.

Dave
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 556 total points
ID: 35502645
heyday2004,

you can rename a file to include square brackets in the name, because the operating system (Windows) accepts these characters in file names. You can also open such a file with Excel and save it. When it comes to creating a file with  Excel, though, you will not be able to even save a new file with square brackets in the name. When an existing file is evaluated within Excel routines like Pivot Table refresh, then it is Excel, not Windows, who is rejecting the source name for the pivot table as invalid. It is still a valid file name for Windows, but it cannot be used as the source of a pivot table.

As you've already noticed and stated above, simply renaming the file will solve the issue.

@Dave, I don't quite see how posting screenshots would do anything to describe the issues any better. Just create a file with some data, save it, close it, rename it to have [] in the name, like My[File]Name.xls, then open it and try to create a pivot table. You won't succeed.

cheers, teylyn
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 224 total points
ID: 35502648
The Windows file manager and DOS DO allow square brackets in file names. Excel does not. It's that simple. The reason is because it conflicts with other elements Excel uses to specify Excel files in references to Excel file parts. For example, in a hyperlink:

   =HYPERLINK("[C:\Full\Path\To\External Workbook.xls]Sheet1!A1","Display Text")

Because Excel uses square brackets to identify the file name, you can't use square brackets in the file name as it will confuse Excel.

Kevin
0
 

Author Comment

by:heyday2004
ID: 35504494
Thanks for all the replies. The problem is in IE, if i open an excel file from a website, it will be saved to temp folder (cache) with a sqaure brackets automatically added in it and I can't rename the cache file. Any hint here on this Microsoft bug? Here is a related link; http://php.kennedydatasolutions.com/blog/2008/02/05/internet-explorer-breaks-excel-pivot-tables/
But I do want to see more options. Thanks a lot.
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35505958
@heyday - I have to agree with everyone else, you can only work around this issue and looks like you found one tip for that.  The al-most simple is Teylyn's suggestion - just rename the file.

Perhaps just a bit simpler - with IE downloads:
One practice that I do (I don't like Excel renaming any files I download) is to right click on the file link in Internet Explorer, then hit "Save As" and then I name the file whatever I want it to be.  All pivot tables that are self-referencing in that file will still have the right connection.

However, I STILL have as yet to find an online link to a pivot table where by downloading the link, Internet Explorer adds square brackets.  I downloaded a couple example tutorial pivots from online with no difficulties and no additional square brackets.  

I can potentially add additional help if you could provide a public reference where this happens to you (or a screen print).

Dave
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 556 total points
ID: 35508289
Dave, see screenshot. It shows the file name of the file in the IE Status Bar and the result of the file in the Excel window title bar. I did not download and open, but clicked to open the file. (Source of file is http://www.timeatlas.com/5_Minute_Tips/Chunkers/Learn_to_use_Pivot_Tables_in_Excel_2007_to_Organize_Data

Heyday, your best bet is to download the file, rename it to your liking and then open it. Don't just click on it to open it from IE.

Or use Firefox.

cheers, teylyn
screenshot.png
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35508701
@teylyn - thanks.  Its good to see a screenshot, so much appreciated.  And it helps me get to another point, as my advice also (re Post: 35505958) is also to use the Save As and name myself.  

However, I'm trying to duplicate the Asker's problem and cannot.  Either I'm doing something wrong, or my configuration doesn't have this failing - if I can discern that, there may be a better solution...

My workstation installation does NOT put the brackets in the filename, using your example.  I left click the link for example spreadsheet and select the OPEN option - and here's what I get:
No Square brackets in filenameDave
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 556 total points
ID: 35508815
I'm using IE 8 on XP SP3, Excel 2010. You?
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35508828
I'm still using IE 7, but on Vista, Excel 2007.
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 556 total points
ID: 35509053
That may account for the difference.

Heyday, what systems are you using?
0
 

Author Comment

by:heyday2004
ID: 35509422
I do appreciate all the replies. I'm using Excel 2007, Windows 7. But I dont think it matters much. I know if I click save as or download the file and then open it, I will not have problem. But things is I do need to find a way to use "open with" in IE to open the excel file. Any further hint? Many thanks!
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35509650
I'd like to help further, but don't see how you get an "open with" prompt in IE.  What version of IE do you have and can you show a screenshot of that?

Sorry - I love screenshots to get right to the point!

Dave
0
 

Author Comment

by:heyday2004
ID: 35747743
Thanks for the replies. I will provide a screenshot later.
0
 
LVL 5

Assisted Solution

by:wellous
wellous earned 108 total points
ID: 35758063
@dlmille, @teylyn,
Yes ,you can rename the xl file on fly using vba to remove the [] from the filename, and see the correct pivot table in ie. pls check this code, cheers,
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_26986678.html?cid=1131#a35502013

Hi Heyday,

Do you still have issues with that file ?
pls advise

Cheers,
Wellous
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1112 total points
ID: 35758077
Thanks - I'm aware of this thread & its solution - which I initially thought as a duplicate.  This question is about understanding the environment where this issue doesn't even happen, if possible.

I cannot reproduce it which is why seeing the Asker's images of the issue might be useful..


Dave
0
 

Author Comment

by:heyday2004
ID: 35813243

Hi, Dave: I'm so sorry that the server was shutdown and I could not take a screenshot. Thanks for your patience.

You are right that I could not repeat it either for the website listed above. But it did happen in my previous download and it's of the same error message you will see when you refresh the pivot table after you rename the excel file with "[]" in the name.

It's not a problem for me for now and thanks for all the kind replies!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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