none
Converting VBA code to C# RRS feed

  • Question

  • In VSTO Excel button click event works great but because of the excelApp.Workbooks.Add() create a new workbook?

    using Excel = Microsoft.Office.Interop.Excel;

    class Program
    {    static void Main(string[] args)
        {

                var excelApp = new Excel.Application();
                excelApp.Workbooks.Add();

                //Insert VBA code here.
                excelApp.ActiveCell.FormulaR1C1 = "1";
                excelApp.Range["A2"].Select();
                excelApp.ActiveCell.FormulaR1C1 = "2";
                excelApp.Range["A1:A2"].Select();
                excelApp.Selection.AutoFill(Destination: excelApp.Range["A1:A10"], Type: Excel.XlAutoFillType.xlFillDefault);
                excelApp.Range["A1:A10"].Select();

                excelApp.Selection.Interior.Pattern = Excel.Constants.xlSolid;
                excelApp.Selection.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
                excelApp.Selection.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1;
                excelApp.Selection.Interior.TintAndShade = 0.399945066682943;
                excelApp.Selection.Interior.PatternTintAndShade = 0;

                excelApp.Range["A1:A10"].Select();
                excelApp.ActiveSheet.Shapes.AddChart.Select();
                excelApp.ActiveChart.ChartType = Excel.XlChartType.xlConeColStacked;
                excelApp.ActiveChart.SetSourceData(Source: excelApp.Range["Sheet1!$A$1:$A$10"]);

                excelApp.Visible = true;


        }

    }

    Problem is I don't want to create a new workbook, I want to execute code on activesheet...

    Friday, May 11, 2018 3:28 PM

All replies

  • Hello,

    You can use the ActiveWorbook property of the Application class instead of adding a new workbook:

                 var excelApp = new Excel.Application();
                
                 //Insert VBA code here.
                 excelApp.ActiveCell.FormulaR1C1 = "1";
                 excelApp.Range["A2"].Select();
                 excelApp.ActiveCell.FormulaR1C1 = "2";
                 excelApp.Range["A1:A2"].Select();
                 excelApp.Selection.AutoFill(Destination: excelApp.Range["A1:A10"], Type: Excel.XlAutoFillType.xlFillDefault);
                 excelApp.Range["A1:A10"].Select();
    
                 excelApp.Selection.Interior.Pattern = Excel.Constants.xlSolid;
                 excelApp.Selection.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
                 excelApp.Selection.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1;
                 excelApp.Selection.Interior.TintAndShade = 0.399945066682943;
                 excelApp.Selection.Interior.PatternTintAndShade = 0;
    
                 excelApp.Range["A1:A10"].Select();
                 excelApp.ActiveSheet.Shapes.AddChart.Select();
                 excelApp.ActiveChart.ChartType = Excel.XlChartType.xlConeColStacked;
                 excelApp.ActiveChart.SetSourceData(Source: excelApp.Range["Sheet1!$A$1:$A$10"]);
    
                 excelApp.Visible = true; 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Friday, May 11, 2018 11:29 PM
  • Hello mainepaine,

    >>var excelApp = new Excel.Application();

    You created a new application and if you want to execute code on an active sheet, you need get the application instance of the active sheet instead of creating an new application instance.

    In VSTO, we could get the instance like below code.

    var excelApp=Globals.ThisAddIn.Application;

    However, it seems that your simply code is not in an VSTO project. If your project is not an VSTO project, I think below link will be helpful for you.

    Get instance of Excel application with C# by Handle

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 1:42 AM
  • Hello mainepaine,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 23, 2018 8:19 AM