VSTO Learning Path: Learning to read and write VBA code using epplus--

To create a xlsm workbook:

Macro workbook, you must have a VBProject object, at least one worksheet

1 stringPath =@"E:\studyvs\open xml\test.xlsm";2 varPackage =Newexcelpackage ();3Package. WORKBOOK.WORKSHEETS.ADD ("Sheet1");4 //Create a Project object5 Package . Workbook.createvbaproject ();6 //Save Workbook7Package. SaveAs (NewFileInfo (path));8System.Windows.Forms.MessageBox.Show ("created successfully");

To write code to a macro file:

1 stringPath =@"E:\studyvs\open xml\test.xlsm";2 varPackage =Newexcelpackage ();3Package. WORKBOOK.WORKSHEETS.ADD ("Sheet1");4 //Create a Project object5 Package . Workbook.createvbaproject ();6 //you can also using the Officeopenxml.vba7OfficeOpenXml.VBA.ExcelVbaProject proj =Package . Workbook.vbaproject;8 //Sheet1 sheet Module9OfficeOpenXml.VBA.ExcelVBAModule Sheetmodule =proj. modules["Sheet1"];Ten //Name the Sheet1 module, consistent with the worksheet name OneSheetmodule. Name ="Sheet1"; ASheetmodule. Code + ="\r\nprivate Sub worksheet_selectionchange (ByVal Target as Range)"; -Sheetmodule. Code + ="\r\nmsgbox (\ "Test of the VBA feature!\") \r\nend sub\r\n"; -  the  - //Workbook Module -OfficeOpenXml.VBA.ExcelVBAModule Workbookmodule =Package . Workbook.codemodule; -Workbookmodule. Name ="Workbook"; + //add code to a workbook module -Workbookmodule. Code + ="\r\nprivate Sub workbook_open () \r\nsheet1. Cells (a). Value = \ "VBA test\""; +Workbookmodule. Code + ="\r\nmsgbox \ "VBA is running!\" \r\nend Sub"; A  at //Create a standard module - varm = package. Workbook.VbaProject.Modules.AddModule ("Module1"); -M.code + ="Public Sub Test (param1 as String) \r\n\r\nend sub\r\n"; -M.code + ="Public Function functest () as String\r\n\r\nend function\r\n"; -  - //Create two classes of modules in varc = Package. Workbook.VbaProject.Modules.AddClass ("Class1",false); -C.code + ="Private Sub Class_Initialize () \r\n\r\nend sub\r\nprivate Sub Class_Terminate () \r\n\r\nend Sub"; to varC2 = package. Workbook.VbaProject.Modules.AddClass ("Class2",true); +C2. Code + ="Private Sub Class_Initialize () \r\n\r\nend sub\r\nprivate Sub Class_Terminate () \r\n\r\nend Sub"; -  the //Set the access password for the project *Proj. Protection.setpassword ("Epplus"); $ Panax Notoginseng //Save Workbook -Package. SaveAs (NewFileInfo (path)); theSystem.Windows.Forms.MessageBox.Show ("Write Success");

For test.xlsm files that already exist, save as Test2.xlsm

1 string @" E:\studyvs\open xml\test.xlsm " ; 2 var New Excelpackage (new  FileInfo (path)); 3 package. SaveAs (new FileInfo (@ "E:\studyvs\open xml\test2.xlsm"));

Read code for each module in Test.xlsm, write to text file

1 stringPath =@"E:\studyvs\open xml\test.xlsm";2 varPackage =NewExcelpackage (NewFileInfo (path));3 foreach(varModuleinchPackage . Workbook.VbaProject.Modules)4 {5File.writealltext (string. Format (@"E:\studyvs\open xml\{0}.txt", module. Name), module. Code);6 }7 8System.Windows.Forms.MessageBox.Show ("OK");

VSTO Learning Path: Learning to read and write VBA code using epplus--