Run VBA macro in Excel sheet using external c# console application

Question

I am trying to invoke one macro which is in Excel file. Below is the code which I got, but on running below it throws exception, with below message.

Code:

// Define your Excel Objects
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = null;

try
{
    //Start Excel and open the workbook.
    xlWorkBook = xlApp.Workbooks.Open(@"C:\MyFolder\Junk\macro\xxxxxReport.xlsm");

    //Run the macros by supplying the necessary arguments
    xlApp.Run("LookupHLink");

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

    //Quit the Excel Application
    xlApp.Quit();
}
catch (Exception ex)
{
}
finally
{
    //~~> Clean Up
    releaseObject(xlApp);
    releaseObject(xlWorkBook);
}

There is no parameter for the macro.

Public Sub LookupHLink()

Error message:

Cannot run the macro 'LookupHLink'. The macro may not be available in this workbook or all macros may be disabled.

Please let me know if I am doing anything wrong.

Is the sub in a regular module or in a Sheet module? If the latter then you may need to use (eg) xlApp.Run("Sheet1.LookupHLink"); where Sheet1 is the name of the worksheet module. — Tim Williams
– Tim Williams, Commented Jul 3, 2015 at 6:54
Tried that also but error again :Cannot run the macro 'XXXX_Detail.LookupHLink'. The macro may not be available in this workbook or all macros may be disabled. — Amit
– Amit, Commented Jul 3, 2015 at 7:07

Theraot · Accepted Answer · 2015-07-03 07:13:56Z

As far as I can tell the possible causes of this error are:

The system located the file but could not load it.
The file was loaded but it has no macros.
The file was loaded but the macros are disabled.
The file was loaded, macros exists... but you misspelled the name.

Given the available information, it seems to be that the more likely cause is disabled macros.

You may try moving your file to a trusted location:

You can see the trusted locations by doing Click File > Options then Click Trust Center > Trust Center Settings > Trusted Locations.

See Change macro security settings in Excel [2010].

You may also try changing the security settings:

In Excel 2007

Click the Microsoft Office Button, and then click Excel Options.
Click Trust Center.
Click Trust Center Settings.
Click Macro Settings.
Click to select the Trust access to the VBA project object model check box.
Click OK to close the Excel Options dialog box.

Source: You may receive an run-time error when you programmatically allow access to a Visual Basic project in Excel 2003 and in Excel 2007 - at Microsoft Knowledge base.

Note: optional parameters in C# are a relatively new feature... you may try the old way and use Type.Missing to fill the infamous 30 args of the Run method. Something like this:

xlApp.Run('macro', Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Collectives™ on Stack Overflow

Run VBA macro in Excel sheet using external c# console application

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related