This repository has been archived by the owner on Mar 9, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1.2k
VBA
Jan Källman edited this page Oct 9, 2017
·
2 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 15...
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;
}
}