none
VSTO Excel Addin and Excel Template Project - Getting the Template to fire _Startup event to fire when a workbook is created RRS feed

  • Question

  • Hi,

    I have a simple question, but I'm sure the answer is complicated.

    I have two projects in my solution.

    1)  ExcelAddin

    2)  ExcelTemplate

    The ExcelAddin has a Ribbon with one button. When a user presses the button, a new Workbook is created using the ExcelTemplate template.

    This all works as expected.

    However, in the ExcelTemplate ThisWorkbook.vb file, there is a method

        Private Sub ThisWorkbook_Startup() Handles Me.Startup
     
        End Sub

    Why does this method not start when the new workbook is opened?  Do I somehow need to add the ExcelTemplate object to the Globals.ThisAddin container?

    If I instead use a normal Excel Template created from Excel, and put a macro folder with the ThisWorkbook_Startup method include, it will fire.  However, when I use the "code behind" document customization ability of VSTO, it doesn't work.

    I am stumped.

    Friday, February 10, 2012 3:26 PM

Answers

  • Hi JasonNoob,

    Thanks for posting in the MSDN Forum.

    I would recommend you clarify your issue more clearly via following options:

    1. Your Office version.
    2. Your Visual Studio version.
    3. Your VSTO runtime version.

    I had reproduce your issue on my side, all works fine. I will show your key snippet for my add-in project and template project. I developed them under Visual Studio 2010, Office 2010, VSTO runtime 4.0

    add-in project's ribbon class:

    Imports Microsoft.Office.Tools.Ribbon
    
    Public Class Ribbon1
    
        Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim app As Excel.Application = Globals.ThisAddIn.Application
    
            app.Workbooks.Add("C:\*****\TestTemplate.xltx")
        End Sub
    End Class

    Template project's ThisWorkbook.vb

    Imports System.Xml
    
    Public Class ThisWorkbook
    
        Private Sub ThisWorkbook_Startup() Handles Me.Startup
            Dim Setting As XmlWriterSettings = New XmlWriterSettings
            Setting.Indent = True
            Setting.IndentChars = "  "
            Dim Writer As XmlWriter = XmlWriter.Create("C:\*****\test.xml", Setting)
            Dim str As String = Date.Now.ToLongDateString & " " & Date.Now.ToLongTimeString
    
            Writer.WriteStartElement("Hello")
            Writer.WriteStartElement("Date")
            Writer.WriteString(str)
            Writer.WriteEndElement()
            Writer.WriteEndElement()
            Writer.Close()
    
            Dim sheet As Excel.Worksheet = Globals.ThisWorkbook.Application.ActiveWorkbook.ActiveSheet
            sheet.Cells(1, 1).Value = str
    
        End Sub
    
        Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown
    
        End Sub
    
    End Class

    When I click ribbon button the template will be used to create a new workbook and DateTime will appear at first cell of the active worksheet. And it will write a xml file on your local disk. I tried it, it works fine. So I think you need provide more details of the code in your ThisWorkbook_Startup() method for further research.

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, February 14, 2012 6:07 AM
    Moderator

All replies

  • Hi JasonNoob,

    Thanks for posting in the MSDN Forum.

    I would recommend you clarify your issue more clearly via following options:

    1. Your Office version.
    2. Your Visual Studio version.
    3. Your VSTO runtime version.

    I had reproduce your issue on my side, all works fine. I will show your key snippet for my add-in project and template project. I developed them under Visual Studio 2010, Office 2010, VSTO runtime 4.0

    add-in project's ribbon class:

    Imports Microsoft.Office.Tools.Ribbon
    
    Public Class Ribbon1
    
        Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim app As Excel.Application = Globals.ThisAddIn.Application
    
            app.Workbooks.Add("C:\*****\TestTemplate.xltx")
        End Sub
    End Class

    Template project's ThisWorkbook.vb

    Imports System.Xml
    
    Public Class ThisWorkbook
    
        Private Sub ThisWorkbook_Startup() Handles Me.Startup
            Dim Setting As XmlWriterSettings = New XmlWriterSettings
            Setting.Indent = True
            Setting.IndentChars = "  "
            Dim Writer As XmlWriter = XmlWriter.Create("C:\*****\test.xml", Setting)
            Dim str As String = Date.Now.ToLongDateString & " " & Date.Now.ToLongTimeString
    
            Writer.WriteStartElement("Hello")
            Writer.WriteStartElement("Date")
            Writer.WriteString(str)
            Writer.WriteEndElement()
            Writer.WriteEndElement()
            Writer.Close()
    
            Dim sheet As Excel.Worksheet = Globals.ThisWorkbook.Application.ActiveWorkbook.ActiveSheet
            sheet.Cells(1, 1).Value = str
    
        End Sub
    
        Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown
    
        End Sub
    
    End Class

    When I click ribbon button the template will be used to create a new workbook and DateTime will appear at first cell of the active worksheet. And it will write a xml file on your local disk. I tried it, it works fine. So I think you need provide more details of the code in your ThisWorkbook_Startup() method for further research.

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, February 14, 2012 6:07 AM
    Moderator
  • Hi JashonNoob,

    Did you solve your issue? I will close this thread due to have no response from you. If you feel my answer doesn't help you, please feel free to unmark it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 7:13 AM
    Moderator