Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4267
  • Last Modified:

How do I insert macro code into Excel Interop Worksheet?

I'm generating Excel worksheets from .Net windows app written in C#. I have a hard time inserting VBA/Macro code into specific worksheets.

I've successfully managed to insert code so it comes out in a Module (Module1), but I want to have the code in the sheets instead to be able to access objects of that sheet.

How do I add code to the sheet?

Furthermore, how do I attach an added control (checkbox) to the VBA macro?

Best regards
// Get the string containing the VBA code
String sCode  = Properties.Resources.ResourceManager.GetString("AssortmentReportVBA");
 
// Create a new VBA code module.
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                
                                
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
 
 
 
 
Worksheet scSheet = book.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
            
scSheet.Name = "Sheet_1";
                                    
// I want this checkbox to run a macro when clicked:
Range viewBoxCursor = viewBoxHeader.get_Offset(1, 0);
            
Shape checkBox = scSheet.Shapes.AddOLEObject("Forms.CheckBox.1", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, viewBoxCursor.Left, viewBoxCursor.Top, viewBoxCursor.Width, viewBoxCursor.Height);
            checkBox.Name = "volumeCheckBox";
            OLEObject sheetBox = (OLEObject)scSheet.OLEObjects(checkBox.Name);
            sheetBox.Object.GetType().InvokeMember("Caption", System.Reflection.BindingFlags.SetProperty, null, sheetBox.Object, new object[] { "Volume" });
 
// All of the above works -  Here's where the trouble starts - how do I add code for this sheet and connect to the macro? This below does not work...
 
scSheet.Scripts.Add(scSheet.Cells.get_Range("A1", "A1"),
        Microsoft.Office.Core.MsoScriptLocation.msoScriptLocationInBody,
        Microsoft.Office.Core.MsoScriptLanguage.msoScriptLanguageVisualBasic,
        "ScriptOne", String.Empty, @"MsgBox ""This is ScriptOne.""");

Open in new window

0
jenmat
Asked:
jenmat
  • 2
2 Solutions
 
JurgenDBCommented:
You can get the code-behind-sheet with something like
book.VBProject.VBComponents("Sheet1")
where Sheet1 is the codename of the sheet
0
 
jenmatAuthor Commented:
Sounds like just what I'm after, but...

VBIDE.VBComponent oModule = book.VBProject.VBComponents("Sheet1");
does not compile:
Microsoft.Vbe.Interop._VBProject.VBComponents' is a 'property' but is used like a 'method'

and when I elaborated and tested
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(scSheet.Name);
Or
VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(scSheet.Name);
it gives exception -  Subscript out of range, so no luck yet.

0
 
jenmatAuthor Commented:
This worked though...

VBIDE.VBComponent oModule = book.VBProject.VBComponents.Item(1);
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now