1

To answer a request from the client, Node.js needs to export charts from Excel files in images into a repository. I chose to use a VBA Macro because I don't think I have other solutions. The VBA code is working properly (when I call it manually) but I wish I could connect it with Node.js events. VBScript allows me to call a VBA Macro in my script.vbs file :

Option Explicit
On Error Resume Next
CallVBA

Sub CallVBA() 

   Dim ApplicationExcel 
   Dim ClasseurExcel 

   Set ApplicationExcel = CreateObject("Excel.Application") 
   Set ClasseurExcel = ApplicationExcel.Workbooks.Open("H:/macrosVBA.xlsm") 

   ApplicationExcel.Visible = False       
   ApplicationExcel.Run "ChartUpload" 
   ApplicationExcel.Quit 

   Set ClasseurExcel = Nothing 
   Set ApplicationExcel = Nothing 

End Sub

My problem now is to run the VBScript in the JavaScript file :

var objShell = new ActiveXObject("WScript.shell");
objShell.run('H:/script.vbs');

I get the error :

ReferenceError: ActiveXObject is not defined

Adding win32ole and winax doesn't change anything, they don't seem to work anymore. I'm looking for your help to have another solution or to fix my error with ActiveXObject.

Thanks in advance

Pol Grisart
  • 179
  • 4
  • 13
  • 1
    Delete `On Error Resume Next` and `CallVBA`. – Vityata May 30 '18 at 13:12
  • Hello, I didn't test the .vbs file yet because the main problem is calling it from the .js file. Thank you anyway for the correction. – Pol Grisart May 30 '18 at 13:14
  • 1
    Possible duplicate of [Run .vbs script with node](https://stackoverflow.com/questions/47218117/run-vbs-script-with-node) –  May 30 '18 at 13:15
  • I tried the program and it works perfectly. However when I apply it to my project with the previous vbs code (without "On Error Resume Next" and "Call VBA") Nothing happens... – Pol Grisart May 30 '18 at 13:40
  • If you remove `CallVBA` then it just defines a sub, but never executes it. – Geert Bellekens Jan 23 '20 at 18:07

1 Answers1

3

Your Node.js is probably 64bit, whilst your Office/Excel is probably the 32bit version.

You need to run the 32bit VBScript interpreter in order to access the 32bit CreateObject("Excel.Application")

const { spawn } = require("child_process");
const bat = spawn("C:\\Windows\\SysWOW64\\wscript.exe", ["H:\\script.vbs"]);
gilbertbw
  • 634
  • 2
  • 9
  • 27
peekpoke
  • 31
  • 1
  • 4