CODEWITHSUNDEEP
c#excelvba
Amit
Amit

Reputation: 33

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

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.

Upvotes: 1

Views: 2890

Answers (1)

Theraot
Theraot

Reputation: 40140

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);

Upvotes: 1

Related Questions