How do I add a Macro to an Excel Sheet using Late Binding?
I had the code working using Excel(2010) PIA:

//create a new VBA control module
oModule = oWorkbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
//Add the VBA macro to the new code module
oModule.CodeModule.AddFromString(sCode)  //sCode is the VBA macro I created;

I now have to support multiple versions of Excel so I have to use Late Binding.
I have been able to create an Excel Worksheet and populate it.
All I have left to do is add a macro to a work book.

Code creating Excel Worksheet:

objClassType = Type.GetTypeFromProgID("Excel.Application");
      oExcelObject = Activator.CreateInstance(objClassType);
      //Get the workbooks collection
      oWorkbooksObject = oExcelObject.GetType().InvokeMember("Workbooks",
          BindingFlags.GetProperty, null, oExcelObject, null);
      //Add a Workbook;
      oWorkbookObject = oWorkbooksObject.GetType().InvokeMember("Add",
          BindingFlags.InvokeMethod, null, oWorkbooksObject, null);
      //Get the Worksheets collection and the first Worksheet
      Parameters = new object[1];
      Parameters[0] = 1;
      oWorksheetsObject = oWorkbookObject.GetType().InvokeMember("Worksheets",
          BindingFlags.GetProperty, null, oWorkbookObject, null);
      oWorksheetObject = oWorksheetsObject.GetType().InvokeMember("Item",
          BindingFlags.GetProperty, null, oWorksheetsObject, Parameters);
ddanbe commented: Good. +8

Recommended Answers

All 6 Replies

Hi, PhilHernandez, welcome here!
Is this meant to be a code snippet?

Yes, this was just some of the code that I am using to get some of my Excel working.
I just need to figure out how to add a macro to an Excel worksheet.

Do I need to post more info?

More info never hurts, but it is fine as it is. Thank you.
Perhaps some moderator can change your contribution into a snippet...

I'm obviously a noob here.
I guess I don't understand what you mean by 'code snippet'.
Should I have posted more code?

Thanks for your response

Well for your info, sometimes I still feel like I'm a noob :-O
A "code snippet" means you can select how you post, you see it in the heading when you start a new thread. If you have some interesting code you like to share, post it as a snippet, else if you have a question, just post it as a normal thread.

try to use a real latebindingapi like these
example7 contains an example to add vba code to a worksheet.

hope it helps

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.