none
Is it possible to add a macro into Excel workbook from .NET app that uses Microsoft.Office.Interop? RRS feed

  • Question

  • Greetings community,

    It’s a question about exporting data from SQL server to Excel worksheets in order to create various reports.

    During these years, my boss wanted his reports in Excel because they are colorful, sortable, and so on. And I was making simple apps that would read database, creating those reports, and export them to Excel tables, using Microsoft.Office.Interop.

    However, my boss’s last request had me scratching behind ear. He asked me to make a report that would have months as columns and our customers in rows, and in cells should be values of total for each month. There also should be a simple graph to show the trend by months.

    So I created asked table and graph for each costumer, but he said (and was right about) it wasn’t very good because display is crowded with all those graphs. He asked only one graph that would change its appearance when he selects one of the customers.

    So I took that workbook, and manually added requested macro and my boss was happy at the moment.

    Still, what if he wanted to make a report when I’m not at work? I need to change my app to create workbook with macro, but I don’t know how to add a macro to that kind of workbook.

    Can anyone help me, please?

    Tuesday, March 1, 2016 11:52 PM

Answers

  • Hi IvicaNesic,

    For the sample code, it adds the code to the module.

    You could get specify item of VBComponents (e.g. sheet1), then add the macro code, for example (C#):

     oModule = oBook.VBProject.VBComponents.Item("Sheet1");

    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.

    • Marked as answer by IvicaNesic Monday, March 7, 2016 5:03 PM
    Monday, March 7, 2016 3:11 AM
    Moderator

All replies

  • Hi IvicaNesic,

    To add macro to excel file by using C#, please refer to:

    # How To Create an Excel Macro by Using Automation from Visual C# .NET

    https://support.microsoft.com/en-us/kb/303872

    On the other hand, for your requirement, I suggest that you could use application-level add-in or document level-add-in to do it by using C# directly.

    # Programming Application-Level Add-Ins

    https://msdn.microsoft.com/library/bb157876(v=vs.110).aspx

    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, March 3, 2016 3:16 AM
    Moderator
  • Hi Starain chen,

    Thank you for your reply. I’ve learned a lot trying around the info from the link you had provided. I had some problems because C# isn’t really my native language; I’m using VB.NET, however I was able to reproduce the project using VB, and I was able to add a module that would do something when called.

    But it wasn’t really what I needed, and my inability to explain what I needed was probably related to my bad English.

    So I’ll try to explain again step by step, hoping I’ll be more understandable this time.

    First, you open new excel workbook, and save it as workbook with macros: .XLSM file type. Then, you open VB editor in it. You don’t add new module, just double-click “Sheet1” object on the left hand side, and VB editor opens something that looks like module, but in the drop-down on the top of it are two items: General (that is present in any module) and Worksheet (when selected, it allows you to add procedures that should be called when some of worksheet’s events take place.

    So when you select Worksheet, VB editor would suggest SelectionChange event which is exactly what I need. With a little of code added in it would look like this

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim WS As Worksheet
        Set WS = ThisWorkbook.Worksheets("Sheet1")
        WS.Range("a1").Value = Target.Address
    End Sub
    

    This code would produce following behavior: whenever user click any cell with mouse, cell “A1” would show the address of clicked cell.

    Of course, my needs about the project in hand are a little bit complicated, but if anyone could show me how to access that “module” (if it is a module at all) where you can add some code to process worksheet’s and workbook’s events, that would solve my problem.

    If you have any suggestions, please leave a reply.


    Sunday, March 6, 2016 3:28 PM
  • Hi IvicaNesic,

    For the sample code, it adds the code to the module.

    You could get specify item of VBComponents (e.g. sheet1), then add the macro code, for example (C#):

     oModule = oBook.VBProject.VBComponents.Item("Sheet1");

    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.

    • Marked as answer by IvicaNesic Monday, March 7, 2016 5:03 PM
    Monday, March 7, 2016 3:11 AM
    Moderator
  • That's it. Thanks.
    Monday, March 7, 2016 5:03 PM