Solved

How do I insert macro code into Excel Interop Worksheet?

Posted on 2008-10-23
4
4,150 Views
Last Modified: 2013-12-17
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
Comment
Question by:jenmat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Assisted Solution

by:JurgenDB
JurgenDB earned 125 total points
ID: 22786922
You can get the code-behind-sheet with something like
book.VBProject.VBComponents("Sheet1")
where Sheet1 is the codename of the sheet
0
 

Author Comment

by:jenmat
ID: 22794355
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
 

Accepted Solution

by:
jenmat earned 0 total points
ID: 22794474
This worked though...

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

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question