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.

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.