Programatically adding macro to Excel - "ThisWorkbook"
 

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.

the link that shows how to programatically add macro o a new module is:
http://support.microsoft.com/kb/303872

i dont want to add it to a new module but to "ThisWorkbook"

Please help

Programatically adding macro to Excel - "ThisWorkbook"
 

Replace these statements in your code

from

// Create an instance of Excel.
oExcel = new Excel.Application();

// Add a workbook.
oBook = oExcel.Workbooks.Add(oMissing);


to
oExcel = new Excel.Application();

oBook = oExcel.Workbooks.open("C:\temp\book1.xls);


I assume you are creating an Excel Application and then opening a workbook.
If you are just adding code to the opened workbook then use this

oBook = Thisworkbook


If you are working in a macro and not C# you need to put a "SET" in front of
each command.

"Aerojade" wrote:

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.

the link that shows how to programatically add macro o a new module is:
http://support.microsoft.com/kb/303872

i dont want to add it to a new module but to "ThisWorkbook"

Please help

Programatically adding macro to Excel - "ThisWorkbook"
 

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:




Excel.Application oExcel;
Excel.Workbook oBook;
VBIDE.VBComponent oModule;
VBIDE.VBProject oMOD;
Office.CommandBar oCommandBar;
Office.CommandBarButton oCommandBarButton;
String sCode;
Object oMissing = System.Reflection.Missing.Value;

// 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_Compo nentType.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.

Please Help.

Programatically adding macro to Excel - "ThisWorkbook"
 

I don't use C# but I think this is the correction

from
// Create a new VBA code module.
oModule =
oBook.VBProject.VBComponents.Add(VBIDE.vbext_Compo nentType.vbext_ct_StdModule);

to
// Create a new VBA code module.
oModule = oBook.VBProject.VBComponents("ThisWorkBook")

"Aerojade" wrote:

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:




Excel.Application oExcel;
Excel.Workbook oBook;
VBIDE.VBComponent oModule;
VBIDE.VBProject oMOD;
Office.CommandBar oCommandBar;
Office.CommandBarButton oCommandBarButton;
String sCode;
Object oMissing = System.Reflection.Missing.Value;

// 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_Compo nentType.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.

Please Help.