0

The overall goal is to run Node.js on a JavaScript file and scrape the output using MS Access VBA. Here's the JavaScript file I'm using to test:

// C:\Users\micha\OneDrive\AppAcademy\Interview Prep\tst.js

var fs = require("fs");
var outputFile = "testLog.txt";

fs.writeFile(outputFile, new Date());

When I run this from the command prompt as follows, it generates a testLog.txt file with a timestamp in the Interview Prep folder.

C:\Users\micha\OneDrive\AppAcademy\Interview Prep>node tst.js

Now I want to trigger the same thing using VBA from within an Access app. I've tried it two ways. The first is quick and dirty, but my understanding is that I can flesh it out with StdOut for the scraping bit. It uses WScript.Shell:

Private Sub runShellTest_Click()
    Dim objShell As Object, objExec As Object
    Set objShell = CreateObject("WScript.Shell")
    Set objExec = objShell.Exec("C:\Program Files\nodejs\node.exe C:\Users\micha\OneDrive\AppAcademy\Interview Prep\tst.js")
End Sub

When I run it, a command prompt briefly flashes onscreen, but I don't get a new testLog.txt.

The other way I tried is even quicker and dirtier:

Private Sub runShellTest_Click()
    MsgBox (Shell("C:\Program Files\nodejs\node.exe C:\Users\micha\OneDrive\AppAcademy\Interview Prep\tst.js", vbNormalFocus))
End Sub

Again, the command prompt flashes up briefly, and there's no new testLog.txt. However, the message box shows a task ID as expected, so I guess... something happened?

Any help is appreciated!

Erik A
  • 31,639
  • 12
  • 42
  • 67
ProdigalBulldog
  • 31
  • 1
  • 4
  • There's no reason to write to a file, just write to standard out and capture it from vba. – DusteD Jun 08 '17 at 08:42
  • I should clarify: Using JavaScript to write a file was just a way of checking to make sure the JavaScript was being run. What I'm really trying to do is scrape all the console.log outputs from a given JavaScript file, and the first thing I tried was capturing objExec.StdOut. When that didn't work, I wanted to see if the problem was the JavaScript not running or the console.logs not being captured. So far, it looks like the former. – ProdigalBulldog Jun 08 '17 at 15:20
  • @DusteD -- how? – ashleedawg Jul 03 '20 at 11:08

2 Answers2

0

When you run it like this, the file will get generated at the Access executable's location. Please check where your Access is installed location, you should find the file there. In node code you will have to also pass the path where the file needs to be generated.

Temp O'rary
  • 5,366
  • 13
  • 49
  • 109
  • I checked the location of msaccess.exe, which is C:\Program Files (x86)\Microsoft Office\root\Office16. The file isn't there. I also originally had an absolute path in my node code for var outputFile, but when running it manually from the command prompt, it worked fine with just a file name since I was aiming for the same directory where tst.js lives. – ProdigalBulldog Jun 08 '17 at 15:23
  • `Wscript.ScriptFullName` will give you the path where the script is currently executing the log file might be there or in its parent folder. Check and let me know. – Temp O'rary Jun 09 '17 at 05:05
0

I'm been playing with that successfully sending xmlhttp request. You will need converting json to vba dictionary. There is a vba class you can use for the conversion.

Cgonzalez
  • 23
  • 4