You can create javascript functions in VBa like so.
** Copied from: How can I URL encode a string in Excel VBA?
** solution by: ozmike
** Add Microsoft Script Control as reference.
Function encodeURL(str As String)
Dim ScriptEngine As ScriptControl
Set ScriptEngine = New ScriptControl
ScriptEngine.Language = "JScript"
ScriptEngine.AddCode "function encode(str) {return encodeURIComponent(str);}"
Dim encoded As String
encoded = ScriptEngine.Run("encode", str)
encodeURL = encoded
End Function
Edit: 17.04.15
This is a similar solution to the above, only it uses a web browser control, that runs the javascript and gives you the result.
The basic idea goes like this:
1) We upload a local html file to a webbrowser control. The html file has in it the javascript function/s.
The html file also has two elements :
<p id='data'>_DATA_</p>
<p id='result'></p>
The first one will hold the data you want to encode (or to act upon).
the DATA is a place holding. Using vba code we will Replace that sting
with the data you want.
After wards, the javascript inside the html file will read what is written in the first element and output the result to the second element.
After this has happened, using VBA the code will read the result from the second element.
Instructions:
1) Create an empty text file named c:\temp.html and paste the following html code into it :
<!-- saved from url=(0014)about:internet -->
<!DOCTYPE html>
<html>
<head>
<script>
function encode1()
{
var sData;
// put the "input" to a variable.
sData = document.getElementById('data').innerHTML;
// do stuff with the data variable, for example
sData = encodeURIComponent(sData);
// write the manipulated data to the "output"
document.getElementById('result').innerHTML = sData;
}
</script>
</head>
<body onload='encode1();'>
<p id='data'>_DATA_</p>
<p id='result'></p>
</body>
</html>
In the vba editor:
2) Insert a UserFform
3) Add a WebBrowser control to the form.
Add the following code to the userform :
Option Explicit
Public Sub MyEncode(str As String)
Const TEMPLATE_HTML As String = "c:\temp.html"
Const OUTPUT_HTML As String = "c:\tempWithData.html"
Dim s As String
s = ReadAllFile(TEMPLATE_HTML)
s = Replace(s, "_DATA_", str)
Call PrintToFile(OUTPUT_HTML, s)
' Load the HTML file to the WebBrowser control
UserForm1.WebBrowser1.Navigate OUTPUT_HTML
' wait for it to finish
While UserForm1.WebBrowser1.ReadyState <> READYSTATE_COMPLETE
DoEvents
Wend
' wait some more
While UserForm1.WebBrowser1.Busy
DoEvents
Wend
' find the html element that holds the result data and put the value
' to the global variable.
g_result = (WebBrowser1.Document.getElementById("result").innerHTML)
' unload the form
Unload Me
End Sub
Finaly, add a module and put in it the following code:
Option Explicit
' this global variable will hold the returning result from the html file
Public g_result As String
' !!!! RUN THIS PROCEDURE !!!
' This should work fast enough so that you will not see the
' form opening and closing.
Public Sub TestEncode()
Dim sResult As String
Load UserForm1
' This runs the web browser control
Call UserForm1.MyEncode("https://stackoverflow.com/")
' The result will be: http%3A%2F%2Fstackoverflow.com%2F
MsgBox g_result
' Last remark: you can of course run the above using data from
' the sheet, for example:
' Call UserForm1.MyEncode(Range("A1").Text)
End Sub
' a helping method- read contents from a file.
Public Function ReadAllFile(fname As String) As String
Dim sFileData As String
Dim FileLength As Long
Dim iFree As Integer
iFree = FreeFile
Open fname For Input As iFree ' Open file for input.
sFileData = Input(LOF(iFree), iFree) ' read all the file
Close iFree ' Close file.
ReadAllFile = sFileData
End Function
' a helping method- write contents to a file.
Public Sub PrintToFile(fname As String, sFileData As String)
Dim iFree As Integer
iFree = FreeFile
Open fname For Output As iFree ' Open file for input.
Print #iFree, sFileData
Close iFree ' Close file.
End Sub
