Through VB.NET, how do you add and edit code modules of an Excel file using Microsoft.Office.Interop.Excel?

Question

I am updating company software written in VB.Net. Our data is stored in MYSQL. One of the features I am trying to update is the ability to create reports. Now these reports are to be generated as excel workbooks by the software. With some difficulty I have gotten to the part where I can create excel workbooks and insert data and even use some excel features through VB.Net Microsoft.Office.Interop.Excel. However, I want to have the ability to create macros/hard coding directly through interop which will make these reports much more beneficial. I would like the ability to use both worksheet level modules and general code modules. I have tried to play with the syntax, but I don't think I am even close. I can't seem to find any clarification on the topic. Thank you for your help.

        Dim adapter As New MySqlDataAdapter("CALL reports_productionschedule();", connection)
        Dim table As New DataTable
        connection.Open()
        adapter.Fill(table)
        connection.Close()


        Dim app As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()

        Call DataTableToExcel(table, wb, "ProductionSchedule")'sub I built to print out Headers 
        'and Data from a datatable to an excel file

        app.Visible = True

        Dim ws As Microsoft.Office.Interop.Excel.Worksheet

        wb.Activate()
        ws = wb.Worksheets(1)
        ws.Activate()

        ws.Range("A1:ZZ100000").Columns.AutoFit()

        ws.Range("A1:ZZ100000").AutoFilter2(Field:=1)

        app.ActiveWindow.SplitColumn = 0
        app.ActiveWindow.SplitRow = 1
        app.ActiveWindow.FreezePanes = True
        
        'everything works to this point. here are things I have tried without success

        Dim strcode As String
        strcode = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf & "End Sub"
        Dim nmod As Microsoft.Office.Interop.Excel.Module = wb.Modules.Add(strcode)

        app.Modules.Add(strcode)

        'I know you can programmatically write code in another workbook through excel, I figure 
        'there has to be a way in VB.Net

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim linenum As Long
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents(2)
        Set CodeMod = VBComp.CodeModule
    
        With CodeMod

        linenum = .CountOfLines + 1
        .InsertLines linenum, "Private Sub Worksheet_Change(ByVal Target As Range)"


        'But none of these key words comes up in VB.Net intellisense

Answer 1

The easiest way to do this would be to use EPPlus. You can write VBA code to the Excel file.

EPPlus supports Creating, Reading and Writing VBA. Execution/Interpretation of VBA code is not supported. Remember that the package must be saved with the extension xlsm. A VBA project is created by the CreateVBAProject() method of the ExcelWorkbook class. This enables you to write VBA code to the project...

The linked page offers an example in C#, which should be easy to convert to VB.NET:

private static void VBASample1(DirectoryInfo outputDir)
{
   ExcelPackage pck = new ExcelPackage();
   //Add a worksheet.
   var ws=pck.Workbook.Worksheets.Add("VBA Sample");
   ws.Drawings.AddShape("VBASampleRect", eShapeStyle.RoundRect);            
   //Create a vba project             
   pck.Workbook.CreateVBAProject();
   //Now add some code to update the text of the shape...
   var sb = new StringBuilder();
   sb.AppendLine("Private Sub Workbook_Open()");
   sb.AppendLine("    [VBA Sample].Shapes(\"VBASampleRect\").TextEffect.Text = \"This text is set from VBA!\"");
   sb.AppendLine("End Sub");
   pck.Workbook.CodeModule.Code = sb.ToString();            

   //And Save as xlsm
   pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-1.xlsm"));
}