Solved

Excel 2011 (Mac) - back slashes are changed to %5c in hyperlinks

Posted on 2014-01-24
17
693 Views
Last Modified: 2014-10-11
We have an excel spreadsheet which has a column of hyperlinks that open pdf documents.  This file is accessed from both Mac and Windows clients.  Just recently the hyperlinks quit working on the Mac clients, giving the error "Cannot open the specified file".  Everything still works fine on the Windows clients.

In troubleshooting the problem, I found that when I edited the hyperlink from a Mac, the back slashes in the path were replaced by %5C.  Doing the same thing from a Windows client, the path shows the back slash correctly and not %5C.  If I try to replace %5C with a back slash \, the Mac will not allow the change to be saved and gives a bonk tone.  I can however replace the %5C with a forward slash / and suddenly the link works again, however then I get a popup that says "Some files can contain viruses or otherwise be harmful to your computer".

This file is maintained by someone who uses a windows client.  When the back slash is changed to a forward slash from a windows machine, then saved, when you edit the hyperlink again it still shows a back slash.  Edited the modified link from a Mac shows the forward slash correctly, as it should.

I do not understand why this problem suddenly popped up.  When I search for the above "Cannot open the specified file" I find many instances of this problem going ten years back.  

I'd rather not have to manually change every link because there are hundreds, and if I do then I have to deal with the security warning I mentioned.  Does anyone know of a way to resolve the "Cannot open the specified file" error without causing the security warning to come up?  Or, if there's not a way to avoid that, how can I suppress the virus warning?

Thanks in advance for your help.
Brian
0
Comment
Question by:BrianBowlin
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 53

Expert Comment

by:strung
ID: 39808087
Per: http://forums.mozillazine.org/viewtopic.php?f=38&t=331740

"The Internet uses UNIX-style path separators, not DOS/Windows-style. This means that directories should *always* be separated by forward slashes, no matter what kind of computer the website is hosted on or what kind of computer you're using. I don't know the specifics off the top of my head, but either it's technically illegal to have an unescaped backslash character in a URL or Internet Explorer is behaving incorrectly by treating it as a path separator. Either way, I would consider "%5C" the correct interpretation of it and consider Internet Explorer's behavior a fluke. The URL should be rewritten as "Dir/File.htm"."

This seems to suggest that you should not be using backslashes in URL's.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39808290
While '\' is technically allowed in Unix filenames, most Unix/Linux/Mac software considers it an 'escape' character meaning that it 'escapes' the meaning of the following characters.  \r for 'carriage return', \n for 'linefeed', and so on.

http://en.wikipedia.org/wiki/Filename
0
 

Author Comment

by:BrianBowlin
ID: 39808722
Thanks for the feedback!

Here's the thing, the person who maintains this spreadsheet isn't manually typing the address to these pdf documents, they are right-clicking the cell and selecting hyperlink, then choosing the appropriate folder and file, then excel creates the address with the back slash.

The other important thing is that she's been doing it exactly this way for over two years now and the Macs have never had any issue.

I hear what you're both saying about not using back slashes, but it seems as though the programmers of Windows Excel 2010 and Mac Excel 2011 have gotten out of sync with each other.  The quick solution would be to manually change the path on all the hyperlinks, and then change them manually every time a new link is added.  Generally things work out best if I don't create processes for my users with a lot of additional steps, but if I can't find another solution, that may be the best bet.  Then the issue becomes how to I suppress the virus warning?
0
 
LVL 53

Expert Comment

by:strung
ID: 39808756
So you are saying this problem only occurs with spreadsheets created on the Windows version of Excel and subsequently opened on a Mac? If the spreadsheets are created on the Mac initially, there would be no problem?
0
 

Author Comment

by:BrianBowlin
ID: 39808773
Yes exactly, the file and hyperlinks are created by Window Excel and cannot be opened by Mac Excel.

I'll try creating the hyperlinks on a Mac and see what happens, but regardless of the results the file will be maintained by a Windows client.
0
 
LVL 53

Expert Comment

by:strung
ID: 39808783
I am not sure what a good answer is here, unfortunately. Macs and Windows computers use different formats for path names for local and server files.  I presume the pdf's are all local or on your network.

I think you are going to find that links created on the Mac do not work in Windows and vice-versa.

URL pathways are common to the two platforms so this problem does not arise with links to the internet or to intranets.

If your pdfs were on an intranet, I suppose you could then use URLs for both platforms.

http://stackoverflow.com/questions/1369147/linking-a-unc-network-drive-on-an-html-page
0
 

Author Comment

by:BrianBowlin
ID: 39808798
Yes, the excel file and the pdfs it points to are all on a network share.  The interesting thing is that when I manually change the back slash to a forward slash on the windows computer, then save the change, it still shows it as a back slash, but the Mac computers can open the link and see it as a forward slash if you edit the hyperlink.  

I still don't understand what caused this behavior to start suddenly.  It was probably a M$ Office update that broke it.  Unfortunately, I don't think you can back out those updates on the Mac like you can on Windows.  I could try removing Office 2011 all together and retesting though, which I'll try just to see what happens.  Of course if that is the issue, I'd have to do the same thing on 12 machines that are spread about 80 miles apart, and keep them from ever reinstalling the updates going forward, which won't be a good plan.  It's probably easier to just change the links manually and go on down the road.

The question is how do you suppress the virus warnings?  I didn't have those popping up prior to all this.
0
 
LVL 53

Expert Comment

by:strung
ID: 39808810
I suppose you could put in a bug report to MS or see if there is anything in their forums.

What program is triggering the virus warning? Is it Excel itself or a third party virus checker?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:BrianBowlin
ID: 39808846
It's not an antivirus program, it's Excel popping up the warning.  It just says "Some files can contain viruses or otherwise be harmful to your computer", and the user has to click ok to get the pdf to open.  Annoying once, but when you're having to open several of these pdfs, it's down right infuriating.

Good suggestion on submitting a bug report.
0
 
LVL 53

Expert Comment

by:strung
ID: 39808854
Blinkety blank Microsoft. Sorry, but I am out of ideas unless you can find a setting in the Excel prefs to suppress the warning. If you have third party virus software as well, maybe you can turn off the built-in Excel virus warnings entirely.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39814950
The option to remove the warning is under Trust Settings (on windows) where you can indicate particular network locations are ok or disable the prompt entirely

MS/Apple have always had this different approach to backslash (well since Dos days at least) and its normally handled seamlessly when exchanging hyperlinks and obviously that's worked in the past'

The best I can suggest is looking at options, advanced, web options and checking if the 'update links on save' has been changed and making sure that the filenames are properly enclosed in quotes

I did find that others have ahd a similar problem:
http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/hyperlink-corrupted-when-saving-excel-as-html/61d61996-86d6-49b2-b487-3a3ca10d0f8d
0
 
LVL 27

Expert Comment

by:serialband
ID: 39815469
Microsoft has the instructions in their knowledge base. https://support.microsoft.com/kb/925757
0
 

Author Comment

by:BrianBowlin
ID: 39815525
Those steps are only for Office 2007 and 2010, the Mac version is 2011.  When this excel file is opened on a Windows client, the hyperlinks do not throw up the virus warning, this only happens on Macs.
0
 
LVL 27

Expert Comment

by:serialband
ID: 39815919
Ok, how about this?
 go to Excel
 Open Preferences
 Click on AutoCorrect in the Authoring section
 Uncheck "Replace Internet and network paths with hyperlinks"


http://www.gilsmethod.com/how-to-disable-automatic-hyperlinks-in-excel-2011
0
 

Accepted Solution

by:
BrianBowlin earned 0 total points
ID: 39816038
Unfortunately, that doesn't suppress the virus warning popup.
0
 
LVL 27

Expert Comment

by:serialband
ID: 39816726
The only other thing I see is the marcro warning under the security section of the preferences.  I don't see any place that has the virus warning popup.

I'm wondering if they have a setting in the plists which are the Mac equivalent of the registry place into .ini files.  I don't know if I have the time to even look at those this week.
0
 

Author Closing Comment

by:BrianBowlin
ID: 40374571
No solution was found for this problem, my users just live with the virus pop up warning.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

705 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

13 Experts available now in Live!

Get 1:1 Help Now