Manage VBA Codes of Excel Macro‑Enabled Workbook

Add a VBA Module in Node.js

The following sample code creates a new workbook and adds a new VBA module and macro code and saves the output in the XLSM format. Once you open the output XLSM file in Microsoft Excel and click the Developer > Visual Basic menu commands, you will see a module named TestModule and inside it, you will see the following macro code.

Sub ShowMessage() {
    MsgBox "Welcome to Aspose!"
}

Here is the sample code to generate the output XLSM file with a VBA module and macro code.

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Create new workbook
const workbook = new AsposeCells.Workbook();

// Access first worksheet
const worksheet = workbook.getWorksheets().get(0);

// Add VBA Module
const idx = workbook.getVbaProject().getModules().add(worksheet);

// Access the VBA Module, set its name and codes
const module = workbook.getVbaProject().getModules().get(idx);
module.setName("TestModule");

module.setCodes("Sub ShowMessage()" + "\r\n" +
"    MsgBox \"Welcome to Aspose!\"" + "\r\n" +
"End Sub");

// Save the workbook
workbook.save(path.join(dataDir, "output_out.xlsm"), AsposeCells.SaveFormat.Xlsm);

Modify VBA or Macro in Node.js

The following sample code loads the source Excel file which has the following VBA or macro code inside it:

Sub Button1_Click() {
    MsgBox "This is test message."
}

After the execution of the Aspose.Cells sample code, the VBA or macro code will be modified as follows:

Sub Button1_Click() {
    MsgBox "This is Aspose.Cells message."
}

You can download the source Excel file and the output Excel file from the given links.

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Create workbook object from source Excel file
const workbook = new AsposeCells.Workbook(path.join(dataDir, "sample.xlsm"));

// Change the VBA Module Code
const modules = workbook.getVbaProject().getModules();
const moduleCount = modules.getCount();
for (let i = 0; i < moduleCount; i++) {
    const module = modules.get(i);
    const code = module.getCodes();
    if (code.includes("This is test message.")) {
        const updatedCode = code.replace("This is test message.", "This is Aspose.Cells message.");
        module.setCodes(updatedCode);
    }
}

// Save the output Excel file
workbook.save(path.join(dataDir, "output_out.xlsm"));

Advanced topics

Manage settings of Excel spreadsheet files with Node.js via C++ Import XML to Excel workbook with Node.js via C++