如何在 VB 中以编程方式创建并调用 Excel 宏

来源：互联网发布：电信机房有多少端口编辑：程序博客网时间：2024/06/05 04:56

1. 在 Visual Basic 中创建一个标准 EXE 项目。默认情况下会创建 Form1。 2. 单击“项目”菜单中的“引用”，然后选中“Microsoft Visual Basic for Applications Extensibility”。 3. 向 Form1 中添加一个 CommandButton。 Private Sub Command1_Click()
       ' Start Excel
       Dim xlapp As Object 'Excel.Application
       Set xlapp = CreateObject("Excel.Application")

       ' Make it visible...
       xlapp.Visible = True

       ' Add a new workbook
       Dim xlbook As Object 'Excel.Workbook
       Set xlbook = xlapp.Workbooks.Add

       ' Add a module
       Dim xlmodule As Object 'VBComponent
       Set xlmodule = xlbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule

       ' Add a macro to the module...
       Dim strCode As String
       strCode = _
          "sub MyMacro()" & vbCr & _
          "   msgbox ""Inside generated macro!!!"" " & vbCr & _
          "end sub"
       xlmodule.CodeModule.AddFromString strCode

       ' Run the new macro!
       xlapp.Run "MyMacro"

       ' ** Create a new toolbar with a button to fire macro...
       ' Add a new toolbar...
       Dim cbs As Object 'CommandBars
       Dim cb As Object 'CommandBar
       Set cbs = xlapp.CommandBars
       Set cb = cbs.Add("MyCommandBar", 1, , True) '1=msoBarTop
       cb.Visible = True

       ' Make it visible & add a button...
       Dim cbc As Object 'CommandBarControl
       Set cbc = cb.Controls.Add(1) '1=msoControlButton

       ' Assign our button to our macro
       cbc.OnAction = "MyMacro"

       ' Set text...
       cbc.Caption = "Call MyMacro()"

       ' Set Face image...
       ' 51 = white hand
       ' 25 = glasses
       ' 34 = ink dipper
       ' etc...
       cbc.FaceId = 51

       ' Pause so you can inspect results...
       MsgBox "All done, click me to continue...", vbMsgBoxSetForeground

       ' Remember to release module
       Set xlmodule = Nothing

       ' Clean up
       xlbook.Saved = True
       xlapp.Quit
      End Sub