I have found references and code on the net which helps me add code in the
form of an Excel Macro. However it shows how to create a new module and then
add functions to it. How to add functions to the thisWorkbook part of the
File.
I am not quite able to understand what you have explained. So am going to try
to be more clear.
I m trying to use a Windows Application written in C# to programmatically
add a macro function into an Excel File. However when i use the following
code in the button click event of my windows application:
// Create an instance of Excel.
oExcel = new Excel.Application();
// Add a workbook.
oBook = oExcel.Workbooks.Open("C:\\test.xls");
//oModule = oMOD.VBComponents("ThisWorkbook");
// Create a new VBA code module.
oModule =
oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
sCode =
"Private Sub Workbook_BeforePrint(Cancel As Boolean)\r\n" +
" msgbox \"VBA Macro called\"\r\n" +
"Cancel=true\r\n"+
"end sub";
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
MessageBox.Show("Macro added");
oExcel.Visible = true;
i find that it executes properly but what it does is... it adds a new module
to the excel file's macro and then writes the function there. However i want
to write my code into the "ThisWorkbook" part of the VBA project because it
is only there that i can control the events occuring in Excel. I know it can
be done usig an addin... but i want to perform it this way instead.