VSTO Excel 2007: Including or Attaching a Workbook / Worksheet to an Add-In

Question

I want to enable / embed an Excel worksheet with a predefined location in an Excel add-in, but I cannot add a Workbook project element to my VSTO project and I cannot add a link to an Excel Workbook project. So, how can I do this?

My goal is to create an Excel add-in that adds a new worksheet to an existing workbook (which is a download from SAP) to aggregate data.

Sven

Answer 1

, . , . . / . SpreadSheet XML (XMLSS), . (xls xlt), . Excel .

VSTO Excel

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        ' Start of VSTO generated code

        Me.Application = CType(Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(GetType(Excel.Application), Me.Application), Excel.Application)

        ' End of VSTO generated code

        'setup a workbook and worksheet for sample code to work
        Dim oWB As Excel.Workbook = Me.Application.Workbooks.Add()
        Dim oWS As Excel.Worksheet = CType(oWB.Worksheets.Add, Excel.Worksheet)

        'create temporary template
        Dim sPath As String = My.Computer.FileSystem.GetTempFileName
        My.Computer.FileSystem.WriteAllBytes(sPath, My.Resources.Book1, False)

        'open with excel
        Dim oTemplate As Excel.Workbook = Me.Application.Workbooks.Add(sPath)

        'specify worksheet from a different workbook
        '   copies the template worksheet into destination workbook
        oTemplate.Worksheets.Copy(oWS)

        'no longer need template
        oTemplate.Close()

        'delete the temporary file
        My.Computer.FileSystem.DeleteFile(sPath)

        'get our worksheet
        Dim oReportWS As Excel.Worksheet = CType(oWB.Worksheets.Item("Template"), Excel.Worksheet)

        'write our data
        CType(oReportWS.Cells(1, 1), Excel.Range).Value2 = "Here I am!"

    End Sub

End Class

Answer 2

, #:

        // setup a workbook and worksheet for sample code to work
        var oWB = Application.Workbooks.Add(missing);
        var oWS = oWB.Worksheets.Add(missing, missing, 1, missing) as Excel.Worksheet;

        // create temporary template
        var sPath = FileSystem.GetTempFileName();
        FileSystem.WriteAllBytes(sPath, Resource.Template, false);

        // open with excel
        var oTemplate = Application.Workbooks.Add(sPath);

        // specify worksheet from a different workbook
        //   copies the first worksheet into destination workbook
        (oTemplate.Worksheets[1] as Excel.Worksheet).Copy(missing, oWS);

        // no longer need template
        oTemplate.Close(false, missing, missing);

        //delete the temporary file
        FileSystem.DeleteFile(sPath);

        var oReportWS = oWB.Worksheets["Template"] as Excel.Worksheet;

        // write our data
        ((oReportWS.Cells[1, 1]) as Excel.Range).Value2 = "Here I am!";