VBA for Excel, how to load a .js file as Jscript Scriptcontrol object

Question

I know it's a bit silly to load a js into VBA, but I need to load the libphonenumber library by Google to perform an analysis of a big bunch of phone numbers.

I tried to adapt the following code borrowed from here, but the compiled library is to big to be inserted into the Vba code.

Is there any way to load the .js library from a file instead? Thanks!

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

UPDATE.

This should be a working code, but for some reason doesn't works:

Function loabdjs(x As String)

    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject

    Dim script As String
    Dim fs As Scripting.TextStream

    ''' CODE : "function encode(str) {return encodeURIComponent(str);}"
    Set fs = fso.OpenTextFile("test.js", ForReading, False)
    MsgBox ("Never reached this point")


    script = fs.ReadAll

    fs.Close

    Dim ScriptEngine As ScriptControl
    Set ScriptEngine = New ScriptControl

    Dim output As String

    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode script

    output = ScriptEngine.Run("encode", x)
    loadjs = output
End Function

Any ideas?

What do you mean by "the compiled library is to big to be inserted into the Vba code"? — Dai, Jun 05 '17 at 19:54
I tried to simply paste it in the addCode method, but it is about 220kb of code, too long for a line, too big to be inserted including underscores. — JMartinez, Jun 05 '17 at 20:35
Try adding a try catch block to the js function with empty catch. — cyboashu, Jun 12 '17 at 19:08
Run the code in the VBA debugger and ensure you have error-handling set-up correctly (i.e. don't use `On Error Resume Next`). It sounds like `OpenTextFile` is failing. There will be an error message displayed somewhere, go find it. — Dai, Jun 12 '17 at 19:58

score 2 · Answer 1 · answered Jun 05 '17 at 19:56

2

Read the library from the filesystem into a string:

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

Dim fs As Scripting.TextStream
Set fs = fso.OpenTextFile( "libphonenumber.js", ForReading, False )

Dim script As String
script = fs.ReadAll

fs.Close()

scriptEngine.AddCode script

answered Jun 05 '17 at 19:56

Dai

141,631
28
261
374

Thanks! Unfortunately I didn't succeed with this code. Tried a little Helloworld test, and still not working. Am I missing something? (see update on post) – JMartinez Jun 12 '17 at 18:50

score 0 · Answer 2 · answered Dec 07 '18 at 07:06

0

may be this:

Function loabdjs(x As String)

...

loadjs = output

End Function

answered Dec 07 '18 at 07:06

DimN

1

score 0 · Answer 3 · answered Aug 11 '19 at 18:32

I know this question is old but for anyone looking for an answer, this is a working example

Sub loadFile()

   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

   Dim fs, f, ts, script

   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.GetFile("C:\test.js")
   Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
   script = ts.ReadAll

   Debug.Print script
   ts.Close

End Sub

VBA for Excel, how to load a .js file as Jscript Scriptcontrol object

UPDATE.

3 Answers3

Linked