-
Notifications
You must be signed in to change notification settings - Fork 302
VBA
Mats Alm edited this page Aug 23, 2020
·
12 revisions
EPPlus supports Creating, Reading and Writing VBA. Execution/Interpretation of VBA code is not supported.
A VBA project is created by the CreateVBAProject() method of the ExcelWorkbook class. This enables you to write VBA code to the project as shown in Sample 21-.NET Framework or Sample 21-.NET Core
)...
private static void VBASample1(DirectoryInfo outputDir)
{
ExcelPackage pck = new ExcelPackage();
//Add a worksheet.
var ws=pck.Workbook.Worksheets.Add("VBA Sample");
ws.Drawings.AddShape("VBASampleRect", eShapeStyle.RoundRect);
//Create a vba project
pck.Workbook.CreateVBAProject();
//Now add some code to update the text of the shape...
var sb = new StringBuilder();
sb.AppendLine("Private Sub Workbook_Open()");
sb.AppendLine(" [VBA Sample].Shapes(\"VBASampleRect\").TextEffect.Text = \"This text is set from VBA!\"");
sb.AppendLine("End Sub");
pck.Workbook.CodeModule.Code = sb.ToString();
//And Save as xlsm
pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-1.xlsm"));
}Remember that the package must be saved with the extension xlsm.
You can also sign your VBA code with a code signing certificate.
Somthing like this...
X509Store store = new X509Store(StoreLocation.CurrentUser);
store.Open(OpenFlags.ReadOnly);
foreach (var cert in store.Certificates)
{
if (cert.HasPrivateKey && cert.NotBefore <= DateTime.Today && cert.NotAfter >= DateTime.Today)
{
pck.Workbook.VbaProject.Signature.Certificate = cert;
break;
}
}See Sample 21 for .NET Core or .NET Framework
EPPlus Software AB - https://epplussoftware.com
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Breaking Changes in EPPlus 8
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Hyperlinks
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- The ExcelRange.Text property
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles