Copy VBA Macro UserForm DesignerStorage from Template to Target Workbook
Possible Usage Scenarios
Aspose.Cells allows you to copy the VBA project from one Excel file into another Excel file. VBA project consists of various types of modules i.e. Document, Procedural, Designer etc. All modules can be copied with simple code but for Designer module, there is some extra data called Designer Storage that needs to be accessed or copied. The following two methods deal with Designer Storage.
Copy VBA Macro UserForm DesignerStorage from Template to Target Workbook
Please see the following sample code. It copies the VBA project from the template Excel file into an empty workbook and saves it as the output Excel file. If you open the VBA project inside the template Excel file, you will see a User Form as shown below. The User Form consists of Designer Storage, so it will be copied using VbaModuleCollection.GetDesignerStorage() and VbaModuleCollection.AddDesignerStorage() methods.
The following screenshot shows the output Excel file and its contents which were copied from the template Excel file. When you click on the Button 1, it opens up the VBA User Form which itself has a command button that shows a message box on clicking.
Sample Code
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
//Create empty target workbook | |
Workbook target = new Workbook(); | |
//Load the Excel file containing VBA-Macro Designer User Form | |
Workbook templateFile = new Workbook(srcDir + "sampleDesignerForm.xlsm"); | |
//Copy all template worksheets to target workboook | |
int sheetCount = templateFile.getWorksheets().getCount(); | |
for(int idx=0; idx<sheetCount; idx++) | |
{ | |
Worksheet ws = templateFile.getWorksheets().get(idx); | |
if (ws.getType() == SheetType.WORKSHEET) | |
{ | |
Worksheet 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."); | |
} | |
}//for | |
//----------------------------------------------- | |
//Copy the VBA-Macro Designer UserForm from Template to Target | |
int modCount = templateFile.getWorksheets().getCount(); | |
for(int idx=0; idx<modCount; idx++) | |
{ | |
VbaModule vbaItem = templateFile.getVbaProject().getModules().get(idx); | |
if (vbaItem.getName().equals("ThisWorkbook")) | |
{ | |
//Copy ThisWorkbook module code | |
target.getVbaProject().getModules().get("ThisWorkbook").setCodes(vbaItem.getCodes()); | |
} | |
else | |
{ | |
//Copy other modules code and data | |
System.out.println(vbaItem.getName()); | |
int vbaMod = 0; | |
Worksheet sheet = target.getWorksheets().getSheetByCodeName(vbaItem.getName()); | |
if (sheet == null) | |
{ | |
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() == VbaModuleType.DESIGNER)) | |
{ | |
//Get the data of the user form i.e. designer storage | |
byte[] designerStorage = templateFile.getVbaProject().getModules().getDesignerStorage(vbaItem.getName()); | |
//Add the designer storage to target Vba Project | |
target.getVbaProject().getModules().addDesignerStorage(vbaItem.getName(), designerStorage); | |
} | |
}//else | |
}//for | |
//Save the target workbook | |
target.save(outDir + "outputDesignerForm.xlsm", SaveFormat.XLSM); |