Calling Excel Macro from C# throwing Compile Error : Sub Or Function not defined

Question

I am creating a web application to replace the current Excel Macro Application. The Excel macro has several forms and modules in it. When I call a macro function in the C# using xlApp.Run method, the function is getting called but throwing error when the function calls other sub functions and uses global public variables.

Errors : 1. Compile Error : Sub Or Function not defined - at "Set svd = m_queries(GetSheetRootName(Sheet1))" 2. Runtime Error 424 : Object Required - at "CompareWorksheetsToCombined"

Code :

Public Function CompareExcels(filePath1 As String, filePath2 As String)
Call MsgBox("Done comparing sheets1.", vbInformation, "Compare Complete")
On Error GoTo CompErr
Call MsgBox("Done comparing sheets2.", vbInformation, "Compare Complete")
Dim compBook As Workbook
Dim book1 As Workbook, book2 As Workbook
Dim sheet, Sheet1 As Worksheet, Sheet2 As Worksheet
Dim svd As ScriptViewDescription, obj
Dim l As Long
Dim sheetName As String
Dim a As Integer

'// Check to make sure we have valid options selected for compare
If ValidateOptions = False Then Exit Function

cmdCompare.Enabled = True

'// Create the comparison output workbook
Set compBook = OpenCompareOutput()

'// Get the workbooks we're comparing
Set book1 = Workbooks(filePath1)
Set book2 = Workbooks(filePath2)

'// Verify if the Summary Sheet Exists and compare the libraries.
'//If (WorksheetExists("[" & filePath1 & "]Summary")) And (WorksheetExists("[" & filePath2 & "]Summary")) Then

    Set Sheet1 = book1.Sheets("Summary")
    Set Sheet2 = book2.Sheets("Summary")

    '//If Sheet1.Cells(2, 3) = Sheet2.Cells(2, 3) Then
       '// a = MsgBox("Attention: You are comparing the data from Same Environment", vbCritical, "Critical Warning")
    '//End If
'//Else
    '//a = MsgBox("Summary Tab does not exist in either Workbook A or B and the Libraries are not compared", vbCritical, "Critical Warning")
'//End If

'// Look for selected sheets and then compare them
For l = 0 To lstSheets.ListCount - 1
    If lstSheets.selected(l) = True Then
        '// reset vars to make sure we don't accidentally re-use from last loop
        Set Sheet1 = Nothing
        Set Sheet2 = Nothing
        Set svd = Nothing

        '// Attempt to load up the vars with the new stuff
        sheetName = lstSheets.list(l)
        Set Sheet1 = book1.Sheets(sheetName)
        Set Sheet2 = book2.Sheets(sheetName)
        Set svd = m_queries(GetSheetRootName(Sheet1))

        '// Check vars and if we're good then compare
        If Not (Sheet1 Is Nothing) _
        And Not (Sheet2 Is Nothing) _
        And Not (svd Is Nothing) Then
            Call CompareWorksheetsToCombined(Sheet1, Sheet2, compBook, svd, txtPrefixA.Text, txtPrefixB.Text)
        End If
    End If
Next l

cmdCompare.Enabled = True
Call MsgBox("Done comparing sheets.", vbInformation, "Compare Complete")

Exit Function
CompErr:
Call MsgBox("Error while attempting to process compares." & vbCrLf & vbCrLf & "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error During Compare")
cmdCompare.Enabled = True
End Function

Please help out.

Answer 1

I wrote some software not too long ago that runs a macro before closing.

Microsoft.Office.Interop.Excel.Document.ActiveDocument.Application.Run("macroName")

See: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

Here is some trimmed up VB using Interop.Word that should be easily portable.

Imports Word = Microsoft.Office.Interop.Word

    Dim objWordApp As New Word.Application
    Dim objDoc As Word.Document

    Private Sub Form1_Load(ByVal sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Establish application path, replace appPath on deployment
        Dim appPath As String = Application.StartupPath()

        'Run application in foreground or background.  
        'If in background (false), be sure to add objDoc.close() and objWordApp.Quit()
        objWordApp.Visible = False

        Dim objDoc As Word.Document = objWordApp.Documents.Open(appPath & "path.to.file", [ReadOnly]:=True)

        objDoc = objWordApp.ActiveDocument

        With objDoc
        ...Manipulate document...
        End With

        'clear objDoc object
        objDoc = Nothing

        'quit msWord
        lblStatus.Text = "Quitting MS Word"
        objWordApp.Quit()

        'clear objWord object
        objWordApp = Nothing

        'close com objects on parent system
        lblStatus.Text = "Releasing COM objects"
        If Not objDoc Is Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objDoc)
        End If
        If Not objWordApp Is Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objWordApp)
        End If

        'set filename
        lblStatus.Text = "Sending BLOB to DB"
        Dim filename As String = Path.GetFileName(saveString)

        'open a filestream
        Dim fs As FileStream = New FileStream(saveString, FileMode.Open, FileAccess.Read)

        'open a binary reader stream
        Dim br As BinaryReader = New BinaryReader(fs)

        'create array to store the BLOB
        Dim bytes As Byte() = br.ReadBytes(Convert.ToInt32(fs.Length))

        'Write the BLOB to the DB and set box as unselected
        Dim cmdStoreBlob As SqlCommand = New SqlCommand("UPDATE TABLENAME SET
            COLUMN = @VAR, [SELECTED] = @VAR2 WHERE [SELECTED] = 'True'", connection)
        cmdStoreBlob.Parameters.Add("@VAR1", SqlDbType.VarBinary).Value = bytes
        cmdStoreBlob.Parameters.Add("@VAR2", SqlDbType.Bit).Value = 0
        cmdStoreBlob.ExecuteNonQuery()

        'close binary reader and file stream
        lblStatus.Text = "Cleaning Up"
        br.Close()
        fs.Close() 'close file stream

        'close SQL server connection
        connection.Close()

        'exit application
        Application.Exit()
    End Sub
End Class

Answer 2

Let's say you have an Excel Macro which looks like this

Sub ShowMsg(msg As String, title As String)
    MsgBox msg, vbInformation, title
End Sub

Here is the C# code to pass arguments to that macro and then call it. (TRIED AND TESTED with VS2010 + Excel 2010)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //~~> Define your Excel Objects
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook xlWorkBook;

            //~~> Start Excel and open the workbook.
            xlWorkBook = xlApp.Workbooks.Open("E:\\Users\\Siddharth Rout\\Desktop\\book1.xlsm");

            //~~> Run the macros by supplying the necessary arguments
            xlApp.Run("ShowMsg", "Hello from C# Client", "Demo to run Excel macros from C#");

            //~~> Clean-up: Close the workbook
            xlWorkBook.Close(false);

            //~~> Quit the Excel Application
            xlApp.Quit();

            //~~> Clean Up
            releaseObject(xlApp);
            releaseObject(xlWorkBook);
        }

        //~~> Release the objects
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

https://social.msdn.microsoft.com/Forums/Lync/en-US/2e33b8e5-c9fd-42a1-8d67-3d61d2cedc1c/how-to-call-excel-macros-programmatically-in-c?forum=exceldev

Answer 3

What was the problem? It didn't work? I can't test those things when I'm at work; everything on my machine is completely locked down. I'm lucky if the computer even turns on...

I know, for sure, the script below works.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            //"http://programmingeveryday.wordpress.com/2013/02/20/run-excel-macro-programmatically-in-c/"
            //~~> Define your Excel Objects
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook xlWorkBook;

            //~~> Start Excel and open the workbook.
            xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\Ryan\\Desktop\\RunMacro.xlsb");

            //~~> Run the macros by supplying the necessary arguments
            xlApp.Run("ShowMessage");


            //~~> Clean-up: Close the workbook
            xlWorkBook.Close(false);

            //~~> Quit the Excel Application
            xlApp.Quit();

            //~~> Clean Up
            releaseObject(xlApp);
            releaseObject(xlWorkBook);
        }

        //~~> Release the objects
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}