关闭

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

1160人阅读 评论(0) 收藏 举报
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
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4290次
    • 积分:100
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档