Solved

How do I insert macro code into Excel Interop Worksheet?

Posted on 2008-10-23
4
4,029 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now