Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 10th, 2010, 10:43 AM
Eve Eve is offline
Registered User
 
Join Date: Jun 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default encoding URL in utf-8 in VBA

I want to encode URL in UTF-8 in VBA. Is there a function i can use?
 
Old June 10th, 2010, 02:18 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

The function I use for URL Encoding in VBA may be found at:

http://www.freevbcode.com/ShowCode.Asp?ID=5137
__________________
Regards,
JP
JP SoftTech
 
Old June 13th, 2010, 04:45 AM
Eve Eve is offline
Registered User
 
Join Date: Jun 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi JP,
Thanks for the code. It's very helpful.
The problem now is thet the moment I take the original word i want to encode from the excel sheet the word changes.

for exaple: in the code below the word I want to encode is in cell (3,1) - "päivä kaupan" but the EncodeStr value is "p?iv? kaupan"


Sub Test()

Dim EncodeStr As String
Dim result As String
EncodeStr = Worksheets("BuildURL").Cells(3, 1).Value
result = URLEncode(EncodeStr)
Worksheets("BuildURL").Cells(4, 1).Value = result

End Sub


Is there a change in the VBA settings I need to do?
 
Old June 13th, 2010, 03:33 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

I don't think those characters are UTF-8. I'd be glad to hear otherwise.


Quote:
Originally Posted by Eve View Post
Hi JP,
Thanks for the code. It's very helpful.
The problem now is thet the moment I take the original word i want to encode from the excel sheet the word changes.

for exaple: in the code below the word I want to encode is in cell (3,1) - "päivä kaupan" but the EncodeStr value is "p?iv? kaupan"


Sub Test()

Dim EncodeStr As String
Dim result As String
EncodeStr = Worksheets("BuildURL").Cells(3, 1).Value
result = URLEncode(EncodeStr)
Worksheets("BuildURL").Cells(4, 1).Value = result

End Sub


Is there a change in the VBA settings I need to do?
__________________
Regards,
JP
JP SoftTech
 
Old June 14th, 2010, 03:59 AM
Eve Eve is offline
Registered User
 
Join Date: Jun 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

According to my understanding UTF-8 Supposed to contain all the charecters (including chines, arabic and so on). I also fined a table (link attached) and it contain ä.

http://www.utf8-chartable.de/unicode-utf8-table.pl

I believe the problem is with the VBA editor.
Do you know anything about it?
 
Old June 14th, 2010, 04:58 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

I think the function is just URL encoding without accounting for those characters. The function will need to be edited.

What I would do is update the first Case statement to include the characters you don't want to convert. For example,

Case 37, 43, 48 To 57, 65 To 90, 97 To 122, 228

will add ä to the list of 'allowed' characters (according to the function) because 228 is the numerical representation of ä.

HTH

Quote:
Originally Posted by Eve View Post
According to my understanding UTF-8 Supposed to contain all the charecters (including chines, arabic and so on). I also fined a table (link attached) and it contain ä.

http://www.utf8-chartable.de/unicode-utf8-table.pl

I believe the problem is with the VBA editor.
Do you know anything about it?
__________________
Regards,
JP
JP SoftTech
 
Old February 10th, 2012, 08:18 PM
Registered User
 
Join Date: Feb 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Cool

Quote:
Originally Posted by JP View Post
The function I use for URL Encoding in VBA may be found at:

http://www.freevbcode.com/ShowCode.Asp?ID=5137
Unfortunately, that code doesn't work! At least, it doesn't work for me in Access 2003. I pasted that code into a module, then in the immediate window I typed the following

Code:
?URLEncode("WTF? This is the test string that fails.")
and hit Enter. This is the result:

Code:
WTF%3F+%DEhis+is+%DEhe+test+string+%DEhat+fails%2E
Notice how the letter "T" (upper or lower case, doesn't matter) is converted to %DE but - and this is the weirdest part - only when followed by the letter "h" (again, upper or lower case doesn't matter)! It's absolutely bizarre! Can anyone explain this to me?





Similar Threads
Thread Thread Starter Forum Replies Last Post
encoding="utf-16" Problem francislang XSLT 7 April 10th, 2015 02:23 AM
encoding="UTF-8" Pankaj C XSLT 3 October 4th, 2007 05:27 PM
How do i encoding a url address kevo PHP How-To 3 July 17th, 2007 03:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.