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:\Users\karti\Desktop\3CD-MRL v4 testing\test.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:\Users\karti\Desktop\3CD-MRL v4 testing\test.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! :)
Edit: Just for debugging, I removed return JSON...; from JS and made it into an alert with alert(...); and also temporarily removed the result = ... line from VBA (as it has no use with an alert). And it works! I received a popup on my screen containing the updated JSON after running the macro. It means that my VBA code is sending the data, it's just not receiving it into the result variable. What could be wrong?
