Run javascript function on cells of CSV / excel file

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

Run javascript function on cells of CSV / excel file

Related

Recent Posts