通过Node.js的C++将VBA宏用户窗体设计器存储从模板复制到目标工作簿

可能的使用场景

Aspose.Cells允许你将VBA项目从一个Excel文件复制到另一个Excel文件。VBA项目由各类模块组成，例如文档、过程、设计器等。所有模块都可以用简单代码复制，但设计器模块还包含额外的数据，称为设计器存储，需要访问或复制。以下两个方法处理设计器存储。

• VbaModuleCollection.getDesignerStorage(string)
• **VbaModuleCollection.addDesignerStorage(string, number[])**

将VBA宏用户表单DesignerStorage从模板复制到目标工作簿

请参阅以下示例代码。它将模板Excel文件中的VBA项目复制到空白工作簿中，并保存为输出Excel文件。打开模板Excel文件内的VBA项目即可看到一个用户表单，其包含Designer Storage，将通过VbaModuleCollection.getDesignerStorage(string)和**VbaModuleCollection.addDesignerStorage(string, number[])**方法复制。

下方截图显示从模板Excel文件复制出来的输出Excel文件及内容，点击按钮1会弹出带有命令按钮的VBA用户表单。

示例代码

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

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

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

// Load the Excel file containing VBA-Macro Designer User Form
const templateFile = new AsposeCells.Workbook(path.join(sourceDir, "sampleDesignerForm.xlsm"));

// Copy all template worksheets to target workbook
const sheets = templateFile.getWorksheets();
const sheetCount = sheets.getCount();
for (let i = 0; i < sheetCount; i++) {
const ws = sheets.get(i);
if (ws.getType() === AsposeCells.SheetType.Worksheet) 
{
const s = target.getWorksheets().add(ws.getName());
s.copy(ws);

// Put message in cell A2 of the target worksheet
s.getCells().get("A2").putValue("VBA Macro and User Form copied from template to target.");
}
}


// Copy the VBA-Macro Designer UserForm from Template to Target 
const modules = templateFile.getVbaProject().getModules();
const moduleCount = modules.getCount();
for (let i = 0; i < moduleCount; i++) {
const vbaItem = modules.get(i);
if (vbaItem.getName() === "ThisWorkbook") 
{
// Copy ThisWorkbook module code
target.getVbaProject().getModules().get("ThisWorkbook").setCodes(vbaItem.getCodes());
} 
else 
{
console.log(vbaItem.getName());

let vbaMod = 0;
const sheet = target.getWorksheets().getSheetByCodeName(vbaItem.getName());
if (sheet.isNull()) 
{
vbaMod = target.getVbaProject().getModules().add(vbaItem.getType(), vbaItem.getName());
} 
else 
{
vbaMod = target.getVbaProject().getModules().add(sheet);
}

target.getVbaProject().getModules().get(vbaMod).setCodes(vbaItem.getCodes());

if (vbaItem.getType() === AsposeCells.VbaModuleType.Designer) 
{
// Get the data of the user form i.e. designer storage
const designerStorage = modules.getDesignerStorage(vbaItem.getName());

// Add the designer storage to target Vba Project
target.getVbaProject().getModules().addDesignerStorage(vbaItem.getName(), designerStorage);
}
}
}


// Save the target workbook
target.save(outputDir + "outputDesignerForm.xlsm", AsposeCells.SaveFormat.Xlsm);