locked
Working with excel macros in Visual studio RRS feed

  • Question

  • HI,

    Currently we are trying to automate unit test script using Visual Studio and C#. The client requirement is that we need to access a excel file with Data ( most of the data is calculated using formulas and macros). And we need to verify this values in the application UI. 

    Concern: We are not sure about accessing Excel macros and formula's and manipulating them to get the Value while executing the scripts. We know that we can access Excel cell values and update/read/edit/write into cell. But not sure about accessing macros and excel formulas that are present in the cell.

    Please let us know How to achieve this. 

    Thanks in Advance.

    Friday, May 5, 2017 12:22 PM

All replies

  • Hello,

    I suggest you use Microsoft.Office.Interop.Excel to automate Excel, then you could use Excel object model to access macro/cell formula. Please visit How to automate Microsoft Excel from Microsoft Visual C#.NET 

    To access cell formula, you could use Range.Formula Property

    To run Excel macro, you could use Application.Run Method

    If you want to access VBE to add/edit/delete macro, you could add reference: Microsoft Visual Basics for Applications Extensibility 5.3. For more information, please visit Visual Basic Extensibility Reference

    The code shows how to add a new subroutine in Module 1

    using Microsoft.Vbe.Interop;
    
      VBProject VBProj = Application.VBE.ActiveVBProject;
                VBComponent VBComp = VBProj.VBComponents.Item("Module1");
                CodeModule CodeMod = VBComp.CodeModule;
                string text = "Sub test()"+ System.Environment.NewLine + @"MsgBox(""Hello"")"+ Microsoft.VisualBasic.Constants.vbCrLf + "End Sub";
                CodeMod.AddFromString(text);

    Regards,

    Celeste


    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 24, 2017 5:54 AM