none
Calling an Excel VSTO Workbook customization from an Excel Addin ribbon RRS feed

  • Question

  • Hi Gang

    I have an excel ribbon - which I'll call A, where I HAVE successfully hooked up the

    _xl_WorkbookOpen(Excel.Workbook Wb)

    method (among other things)

    The fun is, I also have, in many of my excel workbooks a public Method in ThisWorkbook - let's call it

    public void MyOnOpen(){}

    The issue I'm having is calling ThisWorkbook.MyOnOpen() from A._xl_WorkbookOpen
    I've tried
    Wb.MyOnOpen - doesn't see it

    Ive tried using

    var vstoWorkbook = Globals.Factory.GetVstoObject(Wb)

    but still, vstoWorkbook doesn't have the routine

    HELP!!

    Tuesday, October 20, 2015 1:10 PM

Answers

All replies

  • Hi,

    OK - here is the issue, and I know it has to be somewhat stupid

    I'm in the middle of w re-write from VBA to VSTO, and in the existing system, we have a large VBA application that starts up a workbook, and then calls a macro (let's call it MyMacro) using Application.Run(Workbookname.ThisWorkBook.MyMacro)

    As I've said, this has all been ported over to C#, and I have one last crazy thing - how to I run the method that is now in the ThisWorkbook object?

    aka I have a public "ThisWorkbook"

    I've tried implementing an interface, and casting the MyWorkbook object, but no dice

    I'm GUESSING I might have to move the method into my own class, set that class up as a COM object, then use COM to  cast it?

    Help?

    Monday, October 5, 2015 2:59 PM
  • Hi KG2V

    Mmm, your problem description is missing some logical steps that would enable us to fully understand...

    It's not clear exactly where this method is / is supposed to be / under what circumstances it needs to be called?

    First of all, are you porting to a VSTO Excel Workbook or to an Excel add-in?

    Are you wanting to move this method to VSTO or does it remain as VBA code in the/a workbook?

    Is the method particular to the workbook or should it be available "globally"?


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, October 5, 2015 5:38 PM
    Moderator
  • Hi,

    Since you mentioned that "ThisWorkbook", I assumed that you were developing a document customization. We can put the code in "ThisWorkbook_Startup" event if you want to the code was executed when the workbook was opened.

    You can get more detail about events in Office solution from link below:
    Events in Office Projects

    In addition, here is an helpful link for you learning VSTO developing:
    Create VSTO Add-ins for Office by using Visual Studio

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 6, 2015 3:23 AM
    Moderator
  • Sorry about the late reply, forgot to turn on alerting

    The customization is actually THREE parts

    1)a C# DLL - not really a part of this issue

    2)an Add in (ribbon) lets call it A

    3)a document customization (actually a bunch of customized documents which will have common entry routine names - interfaces) - lets call it D

    At one point, I need to be able to have a method in A call a Method in D
    Now, I do NOT insist that the method in D be in ThisWorkbook, that is just where it was in the old VBA code

    I've hooked up the add in and it does fire when workbook D is opened, and I do get a reference to the workbook - so far, so good, I just can't get the reference right to be able to call D.MyRoutine

    Tuesday, October 20, 2015 1:25 PM
  • Hi,

    According to your description, the MyOnOpen method is in ThisWorkbook class, however you call MyOnOpen method through Workbook object, which is incorrect.

    To call the method in ThisWorkbook class, you need to create an instance of it, then call its method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 21, 2015 2:30 AM
    Moderator
  • I'm curious: Why would you need to call a method from the add-in, rather than using the Open event for the workbook, so that it runs on its own when the workbook is opened?

    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Wednesday, October 21, 2015 1:38 PM
    Moderator
  • OK, I finally "twigged" to what it is you're trying to do - sorry it took me so long to realize you're trying to call .NET code to .NET code and not VBA...

    See the following article in the MSDN documentation. Note that it's for exposing code in an Add-in, not for exposing code in a document solution.

    https://msdn.microsoft.com/en-us/library/bb608621.aspx

    It's been a while since I've looked into this, but as I recall the other way around is very complex. See this discussion and the link it contains:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/10decfd2-b1b6-4bf2-aa99-16c1f08b9159/call-code-in-doclevel-assembly-from-addin?forum=vsto


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Wednesday, October 21, 2015 1:50 PM
    Moderator
  • Well, the reason for not using the Open event is that we really aren't doing it on the open (don't ask me why my predecessor 5+ years ago called it OnOpen)

    The basic issue is I have customization that is in common across a SET of spreadsheets in the add in (plus a very large C# DLL with like 45 forms and 60 or so classes) and then code that is specific to a certain spreadsheet IN that spreadsheet (for instance, one has 5 sheets, 3 forms, and a half dozen or so classes)

    The original project was entirely in two VBA projects - an add in (which was the current add in + the DLL) and then a bunch of spreadsheets full of VBA macros.  My job?  Move it all to VSTO for now, and then to web services and the like in PhaseII

    Wednesday, October 21, 2015 2:09 PM
  • Thanks - I'll take a look, but yes, I'm trying to expose the .NET code in a document solution so that I can call it from an Add-in solution.  I CAN already call back from the document to the add-in
    Wednesday, October 21, 2015 2:11 PM
  • I was under the impression that the "ThisWorkbook" class was the globals instance of the workbook
    Wednesday, October 21, 2015 2:13 PM
  • Hi KG2V,

    Base on my test, in document add-in, it will initializes the Globals.ThisWorkbook object, but we can’t get that instance if we call it in other add-in. (Globals.ThisWorkbook is null)

    So you need to create a new instance of ThisWorkbook class if you want to call its method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 22, 2015 5:50 AM
    Moderator
  • Sigh, I can get a reference to the existing ThisWorkbook, but the issue is, you can't see any user defined methods, only what Microsoft has defined.

    In Cindy's link above,

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/10decfd2-b1b6-4bf2-aa99-16c1f08b9159/call-code-in-doclevel-assembly-from-addin?forum=vsto

    The first paragraph talks about what I need to do, but of course, the link in that paragraph is, sadly, broken

    The later paragraphs talk about the opposite direction, calling the add-in from the document

    You'd think it would be easy, I mean calling a VBA macro is trivial, but you can't call a .NET procedure from .NET?

    Monday, October 26, 2015 1:57 PM
  • Does the information in this link help a bit:

    http://blogs.msdn.com/b/andreww/archive/2008/03/20/integrating-doc-level-and-add-in-solutions.aspx.


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, October 26, 2015 5:22 PM
    Moderator
  • Yep - looks like it'll help

    Thank you - Let me digest it and see what comes up

    Now a quick one - I have a spreadsheet - totally empty (it's a test) with just

    privatevoidThisWorkbook_Open()

    {

    MessageBox.Show("The Workbook Open Event Has Fired");

    }

    And it works fine when I open it from the File/Open menu

    BUT when I try and call it from code

    Excel.Applicationxl = Globals.ThisAddIn.Application;

    xl.EnableEvents = true;

    Excel.Workbookwb = xl.Workbooks.Open(Filename: @"TestWorkbook.xlsx", Editable: true, AddToMru: false);

    the workbook opens, but no event fires - and yes, I've set the directory to trusted

    Monday, October 26, 2015 6:46 PM
  • Please start a new question for the new topic :-) No one person knows everything and you limit how many people will see this by "burying" it in this longish thread on a totally different topic.

    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, October 26, 2015 7:12 PM
    Moderator
  • You're right
    Monday, October 26, 2015 7:16 PM