public void Import(string projectName)
{
var target = _environment.ActiveVBProject;
using (Stream file = File.OpenRead(BuildFileName(projectName)))
{
var formatter = new BinaryFormatter
{
AssemblyFormat = System.Runtime.Serialization.Formatters.FormatterAssemblyStyle.Simple,
Binder = new DeserializationAppDomainBinder()
};
VbaCodeComponents result = (VbaCodeComponents)formatter.Deserialize(file);
foreach (var item in result.Components)
{
Vbe.VBComponent module = target.VBComponents.FirstOrDefault(e => e.Name == item.Name);
if (null == module)
{
module = target.VBComponents.Add(Vbe.Enums.vbext_ComponentType.vbext_ct_StdModule);
module.Name = item.Name;
}
else
{
module.CodeModule.DeleteLines(1, module.CodeModule.CountOfLines);
}
module.CodeModule.AddFromString(item.Code);
}
}
}
public void RunExample()
{
bool isFailed = false;
string workbookFile = null;
Excel.Application excelApplication = null;
try
{
// start excel and turn off msg boxes
excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = false;
// create a utils instance, not need for but helpful to keep the lines of code low
Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
XlFileFormat fileFormat = GetFileFormat(excelApplication);
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
workBook.SaveAs(workbookFile, fileFormat);
}
catch (System.Runtime.InteropServices.COMException throwedException)
{
isFailed = true;
HostApplication.ShowErrorDialog("VBA Error", throwedException);
}
finally
{
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
if ((null != workbookFile) && (!isFailed))
{
HostApplication.ShowFinishDialog(null, workbookFile);
}
}
}
