none
Run Excel Macro from C#.net

    Question

  • Hi....

    I wanna run a predefined excel macro from my .net application.

    I am using the following code for doing this and it is throwing an TargetInvocationException-

     

    Thread t = new Thread(delegate()

    {

    Excel.

    Application _excelApp = new Microsoft.Office.Interop.Excel.Application();

     

    Marshal.GetActiveObject("Excel.Application");

    _excelApp.GetType().InvokeMember(

    "Run", System.Reflection.BindingFlags.Default

    |

    System.Reflection.

    BindingFlags.InvokeMethod, null, _excelApp, new

     

    object[] { "SetFont" });

    });

    t.IsBackground =

    true;

    t.Start();

     

    Please tell me that why it is throwing an exception while this code is running perfectly for Word Application.

    Thanks in advance for any help.

    Tarash   

    • Moved by Cindy Meister MVPMVP Tuesday, October 05, 2010 12:21 PM not VSTO-specific (From:Visual Studio Tools for Office)
    Tuesday, October 05, 2010 11:36 AM

Answers

All replies

  • Hi Tarash

    I'm having difficulty understanding what your code is trying to do, here. I get the Application.Run part, it's everything around it...

    1. Which version of Excel are we dealing with?

    2. Why are you using late-binding to call Application.Run instead of using Application.Run?

    3. Why do you have Marshal.GetActiveObject? You're not assigning it to anything; and you're already instantiated a new application object in the line above that. If you don't know why you have it and can't explain it to me - take it out.

    4. What are you intending with the Thread t? Office applications cannot run on multiple threads, they're STA. Comment out everything to do with that.

    That should leave you with the following, which I've put in a try...catch block. What happens when you test it?

    try
    {
    Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application(); 
    
    _excelApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | <br/>System.Reflection.BindingFlags.InvokeMethod, null, _excelApp, new object[] { "SetFont" }); 
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
    
    

    TargetInvocationException means there's a problem with how you're calling the the object. Right off hand, I don't see any error...

    If you call Application.Run instead and that generates an error, it should give you a more informative error message. What do you get?


    Cindy Meister, VSTO/Word MVP
    Tuesday, October 05, 2010 12:34 PM
  • Hi...

    My code is running an Excel macro from my .net (framework 2.0) application. In this macro, I'm setting the default font of Excel for my application only.

    Currently, I'm dealing with Excel 2007 but I have to do this for Excel 2003 and Excel XP also. And I'm doing all this with Windows 7 operating system .

    I  did not understand your second point. Wat I have to do with Application.Run? Please let me explain in detail.

    I tried your code but it also gives the following error message-

    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 8001010d

    What should be the right way to do it?

    Thanks for your reply.

    Tarash

    Wednesday, October 06, 2010 6:31 AM
  • Hi Tarash

    <<I tried your code but it also gives the following error message-

    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 8001010d>

    Thank you for teh additional information about the error and your system. I want to test this for you, but in order to do so efficiently (to try to reproduce the error) I need a bit more information:

    1. Which version of Visual Studio?

    2. (Just double-checking) the name of the macro is SetFont? And could you please copy/paste in the VBA code the macro runs?

    <<I  did not understand your second point.>>

    It's possible to call things in the Excel object model directly. GetType().InvokeMember() is not the only way (late-binding). The alternate method (early-binding) would be to use _excelApp.Run(). My question was why you've chosed to use late-binding, rather than early-binding? A test using early-binding might reveal an even more detailed error message...


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 06, 2010 9:54 AM
  • Hi...

    Thanks for your reply and queries.

    I'm using Visual Studio 2005. And yes, my macro name is SetFont. The code of this macro is following -

    Sub SetFont()
    '
    ' SetFont Macro
    '

    '
        With Selection.Font
            .Name = "Arial Unicode MS"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
    End Sub

    Above code sets the default font for Excel. I have placed this macro in the template file (Book.xltm) of Excel.

    < why you've chosed to use late-binding, rather than early-binding? >

    I understand what you are saying.

    Let me tell you one thing that the error is coming in the following code line

    Excel.

    Application _excelApp = new Microsoft.Office.Interop.Excel.Application();

    I don't understand that why this above code line is generating a COMException.

    Please help me for this.

    Tarash.

    Wednesday, October 06, 2010 11:05 AM
  • Hi Tarash

    <<Let me tell you one thing that the error is coming in the following code line

    Excel.

     

    Ah, that does make a difference. Thank you for pointing this out :-)

    Often, this means that there's a problem with the reference to the Office PIA...

    How many versions of Office do you have installed on your developer machine, and which ones? Or, an alternate question, are you testing this on a machine with a different version of Office installed as when the code was originally written?

    As you've said your application needs to run with three versions of Excel, going back to Excel XP, I do think you're going to have to use late-binding, across the board. I think the following blog article will help you

     

    Application _excelApp = new Microsoft.Office.Interop.Excel.Application();>>
    Cindy Meister, VSTO/Word MVP
    Wednesday, October 06, 2010 11:22 AM
  • Hi...

    I do not get where to find the solution. Your link for the blog article is not clear.

    Please, paste the link again.

    Thanks for your help.

    Tarash.

    Wednesday, October 06, 2010 1:30 PM
  • I do not get where to find the solution. Your link for the blog article is not clear.


    <huh> I was sure I pasted in the link. But I did have some problems submitting this, so it must have gotten lost. Sorry about that, and here it is:

    http://blogs.msdn.com/b/andreww/archive/2008/11/30/starting-or-connecting-to-office-apps.aspx


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 06, 2010 3:45 PM
  • Hi...

    Thanks for the link Sir.

    After reading that link I saw that in that application addins are used. But I'm not using addins in my application.

    As I don't know more about addins I'm unable to understand that which point is useful for my application.

     I tried some code of link in my application but it is still giving the COMException.

    Could you please tell me the exact code for doing this?

    Tarash.

    Thursday, October 07, 2010 7:58 AM
  • Hi Tarash

    The blog article discusses various ways to start Office applications. It doesn't matter whether this is done in an Add-in, a Console app, or a Windows Form.

    <<I tried some code of link in my application but it is still giving the COMException.>>

    Which code did you try? You should be trying Activator.CreateInstance, as that's the only one that will let you start Excel and work with late-binding, so that it doesn't matter which version of Office is installed.

    If you need to program against multiple versions of Office than you cannot use the PIAs. This means you cannot use "new Excel.application". you must use late-binding (InvokeMember).


    Cindy Meister, VSTO/Word MVP
    Thursday, October 07, 2010 9:14 AM
  • Hi...

    As you said I tried the Activator.CreateInstance () method in my application but it was also giving a COMException.

    I wanna tell you about my application that what exactly my application does.

    I’m developing a Typing Tool of Unicode Hindi through which user can type Hindi in various Office applications.

    Working of my application -

    1-      Setup of my application will copy the Excel Macro-Enabled template file (Book.xltm) in the following location-

    C:\Program Files\Microsoft Office\Office12\XLSTART

    This is the location for setting the template file to default for all Excel applications.

    2-      User will open an Excel application. (One thing I wanna clear here that my application doesn’t open an excel application by default. User has to open Excel application by itself.)

    3-       Then user will start our application that is, Typing Tool.

    4-      Now, whenever user types anything, tool will call macro, which is present in Excel application by default, only when my application is running and Caps lock is on.

    Now, here the code for calling excel macro is not working in my application while code for calling a word macro is working properly for all versions of MS Word (2007, 2003 and XP).

    The working of my application for calling a word macro is as same as excel macro.

    The code I used to run a word macro through my application is as follows –

    Thread t = new Thread(delegate()

            {

                object _wordApp = Marshal.GetActiveObject("Word.Application"); //connect to the running word instance through a proxy

     

                _wordApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default

                |

                System.Reflection.BindingFlags.InvokeMethod, null, _wordApp, new

                object[] { "SetFont" });

            });

    t.IsBackground = true;

    t.Start();

    Above code is working nicely with all word applications.

    Now, my question is that why it gives a TargetInvocationException when my code executes to run an excel macro while the concept is as same as the code for running a word macro?

    And, why it gives a COMException whenever I try to create an object of Excel application by all methods that you told me?

    Thanks for your kind help.

    Tarash.

    Thursday, October 07, 2010 12:30 PM
  • Hi Tarash

    1. You get the same error when you use GetActiveObject with Excel?

    2. <<Now, my question is that why it gives a TargetInvocationException when my code executes >>

    If the _excelApp object is null (which is apparently the case), that's why you get this exception when you try to run the macro.

    3. <<And, why it gives a COMException whenever I try to create an object of Excel application by all methods that you told me? >>

    I don't know, but apparently there's something wrong with your installation. Have you tried repairing Office?

    Note: You never did answer the questions I asked in a much earlier message, about the versions of Office installed on the machine where you're seeing these problems.


    Cindy Meister, VSTO/Word MVP
    Thursday, October 07, 2010 2:36 PM
  • Hi....

    I tried once again the following code for running excel macro-

    Thread

     

    t = new Thread(delegate()

    {

     

    object _excelApp = Marshal.GetActiveObject("Excel.Application");

    _excelApp.GetType().InvokeMember(

    "Run", System.Reflection.BindingFlags.Default

    |

    System.Reflection.

    BindingFlags.InvokeMethod, null, _excelApp, new

     

    object[] { "SetFont" });

    });

    t.IsBackground =

    true;

    t.Start();

    There is no problem in creating an object of Excel application but it is giving a COMException when GetType.InvokeMember() is called for second time.

    Means when I run my application (typing tool) and start typing in excel, for the first character it works but as soon as I type second character it gives the following COMException-

    The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

    As you said, there's something wrong with my office installation. May be you are right. For now,I do not have repair my office but I'll do it surely.

    Currently, I'm dealing with Office 2007 version and I'm sorry if I didn't answer your question earlier.

    Thanks for help.

    Tarash.

     

    Friday, October 08, 2010 5:35 AM
  • Hi Tarash

    <<There is no problem in creating an object of Excel application but it is giving a COMException when GetType.InvokeMember() is called for second time.

    Means when I run my application (typing tool) and start typing in excel, for the first character it works but as soon as I type second character it gives the following COMException->>

    Ok. This is again different, but it seems we're making progress :-)

    In this case, I don't think your installation is damaged. It's almost certainly a timing question.

    Again, please remove everything from your code that has to do with the object t (the Thread). Office applications do not support multi- or background threading. They're STA and run synchronously. I suspect this could be a large part of the problem.

    You won't be able to process one character while the user is typing the next one, and so on. If your code should execute on every keypress it will have to finish execution  before it can process the next keypress.


    Cindy Meister, VSTO/Word MVP
    Friday, October 08, 2010 8:09 AM
  • Hi...

    << remove everything from your code that has to do with the object t (the Thread). Office applications do not support multi- or background threading. They're STA and run synchronously. I suspect this could be a large part of the problem. >>

    I agree with your point. But I have still 2 questions about that-

    < remove everything from your code that has to do with the object t (the Thread) >

    1-As you said, I remove everything from my code that has to do with the object t (the thread). But it is still giving following COMException for the very first character-

    An outgoing call cannot be made since the application is dispatching an input-synchronous call. (Exception from HRESULT: 0x8001010D (RPC_E_CANTCALLOUT_ININPUTSYNCCALL))

    < Office applications do not support multi- or background threading. >

    2-If Office applications do not support multi- or background threading then why my code is executing for word macro. I'm using thread for it and it is still working without giving any exception in Word 2007, Word 2003 and Word XP. Could you please tell me that why word doesn't give any exception?

    Thanks.

    Tarash.

    Saturday, October 09, 2010 5:42 AM
  • Hello Tarash,

    I have read  the posts carefully, and done some test to reproduce your

    issue , my actions to resolve this issue are as follows, and it can execute the Macro without exception :

    1.       I create an excel file named Book1.xltm and add the macro:

    Sub SetFont()

        Dim rang As Range

        Set rang = Application.ActiveWorkbook.Worksheets(1).UsedRange   

        With rang.EntireRow.Font

            .Name = "Arial Unicode MS"

            .Size = 20

            .Strikethrough = False

            .Superscript = False

            .Subscript = False

            .OutlineFont = False

            .Shadow = False

            .Underline = xlUnderlineStyleNone

            .ThemeColor = xlThemeColorLight1

            .TintAndShade = 0

            .ThemeFont = xlThemeFontNone

        End With

    End Sub

    2.      I test the file by using C# Console with the code:

    class RunMacroViaReflection

        {

     

            public static void Main(string[] args)

            {

                StartThread();

                Console.ReadLine();

            }

     

            public static void StartThread()

            {

                Thread t = new Thread(

                    delegate()

                    {

                        object _excelApp = Marshal.GetActiveObject("Excel.Application");

     

                        try

                        {

                            using (new ChangeLocalHelper("en-us"))

                            {

                                _excelApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default |

                                    System.Reflection.BindingFlags.InvokeMethod, null, _excelApp, new object[] { "SetFont" });

                            }

                        }

                        catch (Exception ex)

                        {

                           

                            throw ex;

                        }

                    });

     

                t.IsBackground = true;

                t.Start();

            }

    }

     

    As you can see, I add  using (new ChangeLocalHelper("en-us")){…} block,the class ChangeLocalHelper is below:

     

        class ChangeLocalHelper : IDisposable

        {

            private string _localeName;

     

            private string _originalLocale;

     

            public ChangeLocalHelper(string localeName)

     

            {

     

                this._localeName = localeName;

     

                _originalLocale = System.Threading.Thread.CurrentThread.CurrentCulture.Name;

     

                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(this._localeName);

     

            }

     

     

     

            #region IDisposable Members

     

            public void Dispose()

     

            {

     

                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(this._originalLocale);

     

            }

     

           #endregion

        }

    If I do not add the using block, I will also encounter the exception: "Exception has been thrown by the target of an invocation." And the macro can’t be executed.

    My test environment is VS2010 and Office 2010; hopefully it will help you more or less. Please let me known your result.

    Best Regards,

    Pengpeng Song

    Monday, October 11, 2010 4:19 AM
  • Hi...

    I tried your code in my application. But it was also giving the exception "Exception has been thrown by the target of an invocation."

    I tried the following code-

    Thread t = new Thread(delegate()

     

    {

     

        object _excelApp = Marshal.GetActiveObject("Excel.Application");

     

        using (new ChangeLocalHelper("en-us"))

     

        {

     

            _excelApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default

            |

            System.Reflection.BindingFlags.InvokeMethod, null, _excelApp, new object[] { "SetFont" });

     

        }

     

    });

     

     

    t.IsBackground = true;

    t.Start();

    As you did, I also add the using block but it gives exception in _excelApp.GetType().InvokeMember.

    Could you please tell me what is this "en-us" in using block?

    If it is related to the language issue then it may be different for my application as I'm doing this for Devnagari.

    My test environment is VS2005 and Office 2007 in Windows 7 OS.

    Thanks for help.

    Tarash.

    Monday, October 11, 2010 10:15 AM
  • Hi Tarash,

            "en-us" in using block means that change the CurrentCulture in order to settle the related language issues.

            I have found that  a few  reasons can contribute the  exception,"Exception has been thrown by the target of an invocation."So,the CurrentCulture may not be a reason,because the InnerException.Message is diffirent with yours.

    In your program,the InnerException.Message show that "The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))", it's hard to reproduce this exception,but I have collected related information,hopefully these can help you more or less, these links are belows:

    http://msdn.microsoft.com/en-us/library/ms228772.aspx

    http://social.msdn.microsoft.com/forums/en-US/vsto/thread/c11ffffd-0385-40f7-b32f-77077de87617/

    http://www.pcreview.co.uk/forums/thread-3372948.php

    Best Regards,

    Pengpeng Song

           

    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 8:51 AM
    Tuesday, October 12, 2010 2:45 AM
  • Hi Pengpeng Song.

    My work environment is W7 Ultimate 64bits, VS2010 Ultimate and Office 2010 Pro.

    I'm looking for two long weeks to solve the problem of calling a macro CreateGraph() that is within the PERSONAL.XLSX.
    How we can solve this by adding my WinForm code with your?

    #region Open the file
                string app = @"C:/Program Files (x86)/Microsoft Office/Office14/EXCEL.exe";
                string param = arqExcel;
                System.Diagnostics.Process process = System.Diagnostics.Process.Start(app, param);
    #endregion

    Thank you,

    silvio pontes


    silvio pontes

    Saturday, February 25, 2012 1:06 PM