Member Avatar for Trebron

Hi All,
Just thought I would give some thing back to this site as its helped me before with varoius problems anyway I wrote a UDF to enable users to run Macro`s in excel here is the function below

Private Sub RunExcelMacros(ByVal Path As String, ByVal MacroName As String)
        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        Dim oBooks As Excel.Workbooks

        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = False
        oBooks = oExcel.Workbooks

        oBook = oBooks.Open(Path)

        oExcel.Run(MacroName)
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

        TabPage7.Select()
        'btnLoadDTS.Enabled = False
        MsgBox("Macro has ran", MsgBoxStyle.Information)

    End Sub

And to use it just call the udf with the 2 parameters like below

RunExcelMacros("c:\MyExcelBook.xls", "TheNetMacro")

just remember that you have to have an excel sheet with a macro named TheNetMacro or any other name you want to give it.

  • 1 Contributor
  • 1 Reply
  • 11K Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Trebron
Member Avatar for Trebron

Oh and forgot to mention you need to import the name space at the begining as well i.e.

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.