Add VBA Module and Code using Aspose.Cells

Aspose.Cells Product Family
1

Hi,

I am not able to add VBA Module and code using aspose.cells. When I try to run this example and open the file , it gives me that file contains invalid key.

Thanks,
Saurabh

2

@saurabh.arora

I tried the code with the [most recent version] and it is working fine.

Download Links:
Output Xlsm File.zip (7.0 KB)
[Aspose.Cells for Java v17.7.7]
sc.png (258.6 KB)

Java

// Create new workbook
Workbook workbook = new Workbook();

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

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

// Access the VBA Module, set its name and codes
VbaModule 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(dirPath + "output.xlsm", SaveFormat.XLSM);

Please check this screenshot for detail information about Output Xlsm File.

sc.png1281×2286 259 KB

3

I upgraded the version. It is working now. Thanks

4

@saurabh.arora,

Good to know that the latest version fixes your issue now. Feel free to contact us any time if you have further queries or issue, we will be happy to assist you soon.

Thank you.

5

I have one more question , what if my vba code is in text file. How do i insert it in module. Do we have api for that or it only accepts String.

module.setCodes()

6

@saurabh.arora

You should read your file in byte[] and then convert it to String and use the same code given earlier.

Please download the output xlsm and the code file from the links given below.

Download Links
Output-xlsm-and-Code-file.zip (7.2 KB)
sc.png (64.8 KB)

Java

File file = new File(dirPath + "myCode.txt");

// init array with file length
byte[] bytesArray = new byte[(int) file.length()];

FileInputStream fis = new FileInputStream(file);
fis.read(bytesArray); // read file into bytes[]
fis.close();

// -------------------------------------------
// -------------------------------------------
// Now convert byte array into string

String strCode = new String(bytesArray);

// Print your string
System.out.println(strCode);

// -------------------------------------------
// -------------------------------------------
// Rest of code is same
// Create new workbook
Workbook workbook = new Workbook();

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

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

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

module.setCodes(strCode);

// Save the workbook
workbook.save(dirPath + "output.xlsm", SaveFormat.XLSM);

Here is the screenshot for your reference.