Hello.
In my project I have xsl-files with VBA module witch contains macros named “FinalMacros”. At runtime I’m modifining module code (add macro “Auto_open”) and save file. When I open saved file in MS Excell, “Auto_open” macro start executing and throw exception.
The couse of exception is cyrillic names in VBA-project
After macro code replacing and saving file project looks like:
If VBA-project not have cyrillic names, file save correct.
Code snippet:
public class Main {
public static void main(String[] args) throws Exception {
processFile("Example1.xls");
processFile("Example2.xls");
}
public static void processFile(String fileName) throws Exception {
Workbook workbook_;
String appPath = System.getProperty("user.dir");
FileInputStream fileInputStream = new FileInputStream(appPath + "\\" + fileName);
try {
workbook_ = new Workbook(fileInputStream);
workbook_.getWorksheets().setActiveSheetIndex(0);
VbaModuleCollection modules = workbook_.getVbaProject().getModules();
boolean hasFinalMacros = false;
for (int i = 0; i < modules.getCount(); i++) {
VbaModule module = modules.get(i);
String code = module.getCodes();
if (code.toLowerCase().contains("finalmacros")) {
module.setCodes(code + "\n" +
"Private Sub Auto_open()\n" +
" Call FinalMacros\n" +
"End Sub");
break;
}
}
workbook_.save(appPath + "\\" + fileName.replace(".xls", "Result.xls"));
} finally {
fileInputStream.close();
}
}
}
Example files attached.