Let’s say we have the following Macros in Excel
Sub RunMe() MsgBox "Called from VB.net Client", vbInformation, "Demo to run Excel macros from VB.net" End Sub Sub ShowMsg(msg As String, title As String) MsgBox msg, vbInformation, title End Sub
Let’s save the Excel File as Sample.xlsm to C:. If you notice the first macro doesn’t take an argument and the second one take 2 arguments. Let’s try and call that from vb.net.
Now open the a new project in VB.net and put two command buttons on it. Add a reference to Excel Object Library. Covered here (Section: Setting up VB.Net to Work with Excel).
Once you have the references set up, use this code:
Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '~~> Open the Excel file xlApp.Visible = True xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsm") '~~> Run the macros. xlApp.Run("RunMe") End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click '~~> Open the Excel file xlApp.Visible = True xlWorkBook = xlApp.Workbooks.Open("C:\Sample.xlsm") '~~> Run the macros. xlApp.Run("ShowMsg", "Hello from VB.NET Client", "Demo 2nd Button") End Sub End Class
- Button 1
- Button 2
Calling Excel macros from VB.Net is easy as you must have discovered by now.