Execute VBS from Nodejs in background mode (Tasks Scheduler or Windows Service)

Question

I'm trying to convert an Excel File to PDF from a NodeJS App. It works when I start Node in command line, but when I start my app.bat as Windows Service through NSSM or directly with the Tasks Scheduler, it doesn't work anymore.

With the script bellow, I can only see the first row in the log.txt which is the program arguments. If everything was going well, I should see 0, then 1, and 2. Sadly, when Node runs in background, I don't have any number, so i'm guessing the problem is CreateObject("Excel.Application") but i don't know why and how to resolve it.

VBScript :

    Set objFSO=CreateObject("Scripting.FileSystemObject")
    outFile="C:\Users\admin\Documents\Projects\tools\log.txt"
    Set objFile = objFSO.CreateTextFile(outFile,True)
    objFile.Write Wscript.Arguments.Item(0) & " | " & WScript.Arguments.Item(1) & vbCrLf

    Dim oExcel
    Set oExcel = CreateObject("Excel.Application")
    Dim oBook

    objFile.Write "0" & vbCrLf
    Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

    objFile.Write "1" & vbCrLf
    oBook.ExportAsFixedFormat xlTypePDF, WScript.Arguments.Item(1)

    objFile.Write "2" & vbCrLf
    oBook.Close True

NodeJS:

    const {exec} = require('child_process');
    exec('"' + Meteor.settings.directories.tools + 'convertToPDF.vbs" "' + outputServerFilePathChild + '.xlsx" "' + outputServerFilePathChild + '.pdf"', (err, stdout, stderr) => {
      if (err) {
        console.log(stderr);
        reject(new Error(stderr));
      } else {
        if (fs.existsSync(outputServerFilePathChild.replace(/ /g, "%20") + ".pdf")) {
          console.log('rename "' + outputServerFilePathChild.replace(/ /g, "%20") + '.pdf" "' + outputServerFilePathChild + '.pdf"');
          exec('rename "' + outputServerFilePathChild.replace(/ /g, "%20") + '.pdf" "' + outputServerFilePathChild + '.pdf"', (err, stdout, stderr) => {
            console.log("File generated: " + outputServerFilePathChild + ".pdf");
                    resolve(outputClientFilePathChild + ".pdf");
                  });
        } else {
          console.log("File generated: " + outputServerFilePathChild + ".pdf");
          resolve(outputClientFilePathChild + ".pdf");
        }
      }
    });

I also tried things like this :

    const child2 = spawn('start', [
                '"PDFConverter"',
                'cscript',
                '"C:\\Users\\admin\\Documents\\Projects\\tools\\convertToPDF.vbs"',
                '"C:\\Users\\admin\\Documents\\Projects\\reports\\admin\\rebates\\customer1 2019-09-30.xlsx"',
                '"C:\\Users\\admin\\Documents\\Projects\\reports\\admin\\rebates\\customer1 2019-09-30.pdf"'
            ], {
                shell: true,
                windowsHide: true,
            });

However, it's not working either. Does someone have an idea ?

EDIT: This problem seems unresolvable. Some people have asked the same question many years ago, and there is still no answer ...

Your error could be on the `objFile.Write Wscript.Arguments.Item(0) & " | " & WScript.Arguments.Item(1) & vbCrLf` line, maybe the commandline parameters aren't making it to the script. The `Set oExcel = CreateObject("Excel.Application")` is fine. — Étienne Laneville, Nov 07 '19 at 18:27
In the log.txt i have this first line written, and I added this line to debug my script. It was not working before, that's why i added these lines. So the problem is not here. — Holgrabus, Nov 08 '19 at 08:39

Holgrabus · Accepted Answer · 2020-01-03T10:39:08.523

I found the answer (but it's not really a solution). As i guessed, the problem is related to Excel, so it's not VBScript or NodeJS. I used EdgeJs to do the same thing in C# and i have the same problem.

You can edit/create Open XML files with their library, but you can't save in PDF or use any macro in server mode.

Microsoft answer :

https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office?wa=wsignin1.0%3Fwa%3Dwsignin1.0

If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully.

The CreateObject function and the CoCreateInstance function return one of the following run-time error messages and cannot be started for Automation... ... ... ...

I also tried with winax (node addon using node-ffi), same result. It works in client mode, but not in server mode (DispInvoke: Open Microsoft Excel cannot access the file).

I can confirm this. I used to run my excel VBA automations to send emails and VBS automations invoked by another nodejs script VIA task scheduler in Windows 7. But after an upgrade to Windows 10, everything is now failing with the errors mentioned in the article you shared. Everything works fine if I manually invoke the script or open the excel file. I am gonna try to make a nodejs program to run forever to trigger these scripts for me. Let me know if you have tried it and if it worked? It might save me some effort. — Vikas Gautam, Sep 07 '20 at 23:00

Execute VBS from Nodejs in background mode (Tasks Scheduler or Windows Service)

1 Answers1

Linked