• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

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

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
BrianBowlin
Asked:
BrianBowlin
  • 7
  • 5
  • 3
  • +2
1 Solution
 
strungCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
BrianBowlinAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
strungCommented:
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
 
BrianBowlinAuthor Commented:
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
 
strungCommented:
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
 
BrianBowlinAuthor Commented:
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
 
strungCommented:
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
 
BrianBowlinAuthor Commented:
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
 
strungCommented:
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
 
regmigrantCommented:
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
 
serialbandCommented:
Microsoft has the instructions in their knowledge base. https://support.microsoft.com/kb/925757
0
 
BrianBowlinAuthor Commented:
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
 
serialbandCommented:
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
 
BrianBowlinAuthor Commented:
Unfortunately, that doesn't suppress the virus warning popup.
0
 
serialbandCommented:
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
 
BrianBowlinAuthor Commented:
No solution was found for this problem, my users just live with the virus pop up warning.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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