Inserting Hyperlink to a file with an apostrophe in the filename (VBA)

lp3535
lp3535 used Ask the Experts™
on
I'm using VBA to insert a link to a file that has an apostrophe in it's filename, but when the link is inserted, it doesn't work because it chops the filename off after the apostrophe.

Any suggestions for how to handle this?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ensure that you escape special characters in your URL using "percent" escape codes.

http://www.w3schools.com/tags/ref_urlencode.asp

The escape code for an apostrophe is %27

So if your url was:   http://somewebsite.com/joe's_dessert_recipe.doc

Instead put:    http://somewebsite.com/joe%27s_dessert_recipe.doc

Remember to escape spaces, doublequotes etc. properly. If you do not escape special characters in your URLs properly they are malformed URLs. Word and other web browsers *might* handle it properly, but it is not guaranteed.

Author

Commented:
Thanks Frosty,

That worked to insert the hyperlink, and it all looks correct, except when I follow the hyperlink, it cannot open the file, and the file path has the %27 instead of the apostrophe.  

I've used the %20 for the spaces, which all works fine, but it doesn't work for the apostrophe.

Any thoughts?

Author

Commented:
Sorry, I should add that the link to a word doc is being inserted into an email, and it's always worked, except now that someone has a file with an apostrophe in it's name.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hm... maybe Word does not handle the more obscure URL escape codes. Is this Word 2003? Can you post the couple lines of code that you use to generate and insert the hyperlink?

Author

Commented:
It works for the spaces, but not apostrophes.

Funnily enough, if I hover over the link when it's created, the link looks to be correct, but when I click on it, it shows the %27 where the apostrophe should be, and therefore can't find the file.
Dim strLink As String
Dim currname As String
currname = Replace(thisDoc.FullName, " ", "%20")
strLink = Replace(currname, "'", "%27")

strBody = "<A HREF=" & "file://" & strLink & ">Threats Notification Form</A>"

Open in new window

Hmm... only thing I can see in your code is that your A HREF tag doesn't have doublequotes. Not sure if that is causing your problem, but right now your output will look like:

<a href=file://etcetcetc>

Which isn't valid, really you want this:

<a href="file://etcetcetc">

Change your code to look like this:

strBody = "<A HREF=""" & "file://" & strLink & """>Threats Notification Form</A>"
Actually if you make the above change, you may be able to *not* do the conversion from apostrophe to %27 and have it work. The missing doublequotes explains why your url was getting truncated at the apostrophe.

Author

Commented:
Thanks - that's perfect.  You're right, I don't need to worry about the apostrophe either.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial