0

I followed Peter's answer here to create a VBA macro that pushes data to a Google Form by building a URL and sending it through an http object. I would like to accomplish this without having to open Excel, so I'm converting the VBA code to VBS and pulling data from a text document. I got the VBA version working, but I can't seem to get the VBS version to submit right, and need some help. I'm still a beginner at VBS.

When I use Peter's code as written, this block gives me an error: "ActiveX component can't create object: 'MSScriptControl.ScriptControl'

Set http = CreateObject("MSXML2.XMLHTTP")
Set ScriptEngine = CreateObject("MSScriptControl.ScriptControl")
ScriptEngine.Language = "JScript"
ScriptEngine.AddCode "function encode(str) {return encodeURIComponent(str);}"

When I remove all but the "Set http = CreateObject("MSXML2.XMLHTTP") line, the code runs fine, but the data ends up in the connected Google Sheet in a single cell with each entry separated by a comma, instead of each entry in a separate cell like normal. I think it has something to do with how the URL is formatted when it's sent from the VBScript as opposed to the VBA macro.

In order for the VBA macro to work, I had to check Microsoft XML.V3.0 in the VBA Reference menu, which I think enabled the ScriptEngine portion of the code to work, but I don't know what the equivalent of adding a reference is in VBS, or if it's even required.

Here is how I modified Peter's code for VBS:

Dim ScriptEngine
Dim http
Dim myURL
Dim strLine
Dim exportLine
Set http = CreateObject("MSXML2.ServerXMLHTTP")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDataFile = objFSO.OpenTextFile("data_file.csv", ForReading)

strLine = objDataFile.ReadLine
exportLine = Split(strLine,",")

myURL = "https://docs.google.com/forms/d/e/ . . . . /formResponse?" & _
        "entry.xxxxxxxxx=" + exportLine(0) + _
        "&entry.yyyyyyyyy=" + exportLine(1) + _
        "&entry.zzzzzzzzz=" + exportLine(2).Text
http.Open "GET", myURL, False
http.setRequestHeader "User-Agent", "Google Chrome 70.03538.102 (compatible; MSIE _ 
6.0; Windows NT 5.0)"
http.send
Brian
  • 66
  • 6
  • Assuming `http` and `scriptEngine` were declared `As Object`, then the code is entirely late-bound and there was never a need to reference any libraries. does the VBScript code not work? – Mathieu Guindon Jan 21 '20 at 20:17
  • The script runs without any errors, but when it puts the data into the Google Form, it all ends up in one cell separated by commas, rather than with each entry in a different cell in the same row. That's what leads me to think the issue is in omitting the encode function. But when I include the `Set ScriptEngine = CreateObject("MSScriptControl.ScriptControl")`, it does give an error. – Brian Jan 21 '20 at 20:29
  • FWIW `exportLine = Split(strLine,",")` is what's responsible for the comma-separated values... the url encoding just ensures the string is valid in a url (no spaces, accents, etc.) – Mathieu Guindon Jan 21 '20 at 20:38
  • Are you running that script on the same machine that was running the vba code? – Mathieu Guindon Jan 21 '20 at 20:39
  • I just figured out what's wrong. I was using a loop to build the URL with one array of the entry IDs (xxxxx,yyyyy, etc) and another array of the data, instead of as it's written above. I had forgotten to increment the ID array each loop, so all the entry IDs were the same in the generated URL, hence all the data ended up in the same form response cell. I both hate it and love it when it's a simple fix. Thank you anyway for your time. – Brian Jan 21 '20 at 20:46

0 Answers0