如何通过VB.NET的Microsoft.Office.Interop.Excel添加编辑Excel代码模块

要实现通过VB.Net的Microsoft.Office.Interop.Excel向生成的工作簿添加VBA宏（包括标准模块和工作表级模块），需完成以下关键步骤：

1. 必要引用与配置 #

• 添加项目引用：在VB.Net项目中添加Microsoft Visual Basic for Applications Extensibility 5.3引用（可通过NuGet或COM引用列表找到）
• Excel信任设置：打开Excel选项 → 信任中心 → 信任中心设置 → 宏设置 → 勾选"信任对VBA项目对象模型的访问"（否则代码会抛出权限异常）

2. 完整代码示例 #

以下是整合你现有功能，并添加宏代码的完整实现：

Imports Microsoft.Office.Interop.Excel
Imports VBIDE ' 需添加VBA Extensibility引用

' 原有数据获取代码
Dim adapter As New MySqlDataAdapter("CALL reports_productionschedule();", connection)
Dim table As New DataTable
connection.Open()
adapter.Fill(table)
connection.Close()

' 原有Excel工作簿创建与数据填充代码
Dim app As New Application
Dim wb As Workbook = app.Workbooks.Add()
Call DataTableToExcel(table, wb, "ProductionSchedule")
app.Visible = True

Dim ws As Worksheet = 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

' --------------------------
' 新增：添加标准模块宏代码
' --------------------------
Dim standardModule As VBComponent = wb.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)
Dim standardCodeMod As CodeModule = standardModule.CodeModule

' 插入通用宏代码示例
Dim standardCode As String = $"
Sub HelloWorld()
    MsgBox(""报表宏已生效"")
End Sub
"
standardCodeMod.AddFromString(standardCode)

' --------------------------
' 新增：添加工作表级Change事件代码
' --------------------------
Dim sheetVBComp As VBComponent = wb.VBProject.VBComponents(ws.Name)
Dim sheetCodeMod As CodeModule = sheetVBComp.CodeModule

' 检查是否已存在Worksheet_Change事件，避免重复添加
Dim eventExists As Boolean = False
For i As Integer = 1 To sheetCodeMod.CountOfLines
    If sheetCodeMod.Lines(i, 1).Contains("Worksheet_Change") Then
        eventExists = True
        Exit For
    End If
Next

If Not eventExists Then
    Dim sheetEventCode As String = $"
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 示例：当单元格内容变化时弹出提示
    If Not Intersect(Target, Range(""A:A"")) Is Nothing Then
        MsgBox(""A列内容已修改："" & Target.Value)
    End If
End Sub
"
    ' 在模块末尾插入代码
    sheetCodeMod.InsertLines(sheetCodeMod.CountOfLines + 1, sheetEventCode)
End If

关键说明 #

• 你之前的错误在于：
  • 未正确引用VBIDE命名空间，导致相关对象无智能提示
  • 使用了VB6风格的Set关键字（VB.Net中无需使用）
  • 错误调用wb.Modules.Add(strcode)，正确方式是通过VBProject.VBComponents添加模块并写入代码
• 操作VBA项目时需确保Excel进程有足够权限，否则会抛出COMException
• 建议添加异常捕获，处理权限或引用缺失的情况

内容的提问来源于stack exchange，提问作者Luke Krell