encoding URL in utf-8 in VBA

Eve · June 10th, 2010, 10:43 AM

I want to encode URL in UTF-8 in VBA. Is there a function i can use?

JP · June 10th, 2010, 02:18 PM

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

http://www.freevbcode.com/ShowCode.Asp?ID=5137

Eve · June 13th, 2010, 04:45 AM

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?

JP · June 13th, 2010, 03:33 PM

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

Quote:

Originally Posted by Eve

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?

Eve · June 14th, 2010, 03:59 AM

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?

JP · June 14th, 2010, 04:58 PM

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

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?

fatman45 · February 10th, 2012, 08:18 PM

Quote:

Originally Posted by JP

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?