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
– Dai, Commented Jun 5, 2017 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
– JMartinez, Commented Jun 5, 2017 at 20:35
Try adding a try catch block to the js function with empty catch. — cyboashu
– cyboashu, Commented Jun 12, 2017 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
– Dai, Commented Jun 12, 2017 at 19:58

Dai · Accepted Answer · 2017-06-05 19:56:45Z

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 5, 2017 at 19:56

Dai

158k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JMartinez Over a year ago

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)

DimN · Accepted Answer · 2018-12-07 07:06:39Z

0

may be this:

Function loabdjs(x As String)

...

loadjs = output

End Function

answered Dec 7, 2018 at 7:06

DimN

1

Comments

Dave Waters · Accepted Answer · 2019-08-11 18:32:53Z

0

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

answered Aug 11, 2019 at 18:32

Dave Waters

11 bronze badge

Collectives™ on Stack Overflow

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

UPDATE.

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE.

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related