I am trying to execute some JS code inside my excel workbook, so I found out about the Internet Explorer & Microsoft HTML references, and I built this code with some inspiration from https://stackoverflow.com/a/31532085 -
Dim hf As Integer: hf = FreeFile
Dim data As String
Open "C:UserskartiDesktop3CD-MRL v4 testing est.json" For Input As #hf
data = Input$(LOF(hf), #hf)
Close #hf
Debug.Print data
' json data received from file, now sending it to the js code
Dim objIE As SHDocVw.InternetExplorer
Dim currentWindow As HTMLWindowProxy
Dim Url As String: Url = "C:UserskartiDesktop3CD-MRL v4 testing est.html"
Dim codez As String: codez = "func = function(){return myFuncReturns('" & data & "');}; retVal = func();"
Set objIE = New SHDocVw.InternetExplorer
objIE.navigate Url
objIE.Visible = 0
Do While objIE.readyState <> 4
DoEvents
Loop
Set currentWindow = objIE.document.parentWindow
currentWindow.execScript code:=codez
Dim result As Long
result = objIE.document.Script.retVal 'here i am supposed to get the updated json
Debug.Print result
objIE.Quit
Set objIE = Nothing
and here's my test.html file's code (which is set to the URL variable in VBA code) -
<!-- saved from url=(0016)http://localhost -->
<html>
<head>
<script type="text/javascript">
function myFuncReturns(json) {
let jsonObject = JSON.parse(json);
jsonObject["status"] = "done";
return JSON.stringify(jsonObject);
}
</script>
</head>
</html>
My JS code is very simple, receive json in string, convert to object, add a parameter named status to the json, re-convert updated object to string, and return the string json to vba. In my VBA code, I just read data from a local json file, send it to my html file using IE reference, and receive updated json string into the result variable - but I am getting 'Runtime error 438 - Object doesn't support this property or method' error and this gets highlighted, with result=0 showing in debugger -
What's wrong with my code? What should I do? Kindly guide... Thanks! :)
