Guys all hello, tell me please, how can I add a VBA script to the xlsx file?

[inerc]

I was able to create a file, but before creating it, I need to create and put a file VBA module. How can I do it?

` function createXLSM(data, user_id) {
/* require XLSX */

    function datenum(v, date1904) {
        if(date1904) v+=1462;
        var epoch = Date.parse(v);
        return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    }

    function sheet_from_array_of_arrays(data, opts) {
        var ws = {};
        var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
        for(var R = 0; R != data.length; ++R) {
            for(var C = 0; C != data[R].length; ++C) {
                if(range.s.r > R) range.s.r = R;
                if(range.s.c > C) range.s.c = C;
                if(range.e.r < R) range.e.r = R;
                if(range.e.c < C) range.e.c = C;
                var cell = {v: data[R][C] };
                if(cell.v == null) continue;
                var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

                if(typeof cell.v === 'number') cell.t = 'n';
                else if(typeof cell.v === 'boolean') cell.t = 'b';
                else if(cell.v instanceof Date) {
                    cell.t = 'n'; cell.z = XLSX.SSF._table[14];
                    cell.v = datenum(cell.v);
                }
                else cell.t = 's';

                ws[cell_ref] = cell;
            }
        }
        if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
        console.log(ws);
        return ws;
    }

    /* original data */


    var ws_name = "SheetJS";

    function Workbook() {
        if(!(this instanceof Workbook)) return new Workbook();
        this.SheetNames = [];
        this.Sheets = {};
    }

    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);

    /* add worksheet to workbook */
    console.log(wb)
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;
    XLSX.writeFile(wb, 'test '+ user_id +'.xlsx');

    return 'test '+ user_id +'.xlsx';
}`

[SheetJSDev]

If you have an existing file with a pre-built VBA module, you can add it to the file by setting the vbaraw field to the contents of the container. The easiest way to do this is to read a file with bookVBA:true in the options, then copy the vbaraw entry to your new workbook.

For example, in node:

var XLSX = require('./')
var wbold = XLSX.readFile("file_with_vba_module.xlsm", {bookVBA:true}); // <-- read original file

var wsdata = [
    [ "a", "b", "c" ],
    [  1 ,  2 ,  3  ]
]

var wbnew = {
    SheetNames:["Sheet1"],
    Sheets:{Sheet1:XLSX.utils.aoa_to_sheet(wsdata)}
};
wbnew.vbaraw = wbold.vbaraw; // <-- copy the vba module

XLSX.writeFile(wbnew, 'newfile.xlsm'); 

Note: You have to use xlsm or xlsb file extension -- XLSX does not support VBA.

We don't yet have a way of generating the module from scratch.

[Rahul-Bhargav]

@SheetJSDev That did not work out for me...
This is what I did.

const wbold = XLSX.readFile('./Utility/VBTemplate.xlsm', { bookVBA:true });
const wbnew = XLSX.readFile(fileName + '.xlsx');
wbnew.vbaraw = wbold.vbaraw; 
XLSX.writeFile(wbnew, 'test' + fileName + '.xlsm');

[SheetJSDev]

If you are trying to copy over worksheet-level macros, the destination file has to have the same worksheet names as the source files. Straight modules should be preserved irrespective of the sheet names. For example, from the test suite download the number_format.xlsm file and using a simple XLSX file
test626.xlsx :

const XLSX = require('xlsx');
const wbold = XLSX.readFile('number_format.xlsm', { bookVBA:true });
var fileName = "test626"
const wbnew = XLSX.readFile(fileName + ".xlsx")
wbnew.vbaraw = wbold.vbaraw
XLSX.writeFile(wbnew, 'test' + fileName + '.xlsm');

Using the latest version on NPM, the resulting file is testtest626.xlsx -- remember to rename as xlsm, github doesnt support XLSM uploads so we had to rename. And if you look in the VB editor you'll see the Module1 from the original file.

If you can share both the template and the data file, we can take a look. Send to dev@sheetjs.com