Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

piofenton (Instructor)
3 Aug 06 9:38
I am trying to call an Excel Macro from .Net. The macro is called DoKbTest and is stored in book1.xls
However when I run the code I get the following error "An unhandled exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll. Additional information: Exception has been thrown by the target of an invocation. " when executing the RunMacro(oExcel, new Object[]{"DoKbTest"}); line. The macro works perfectly when called from excel.
Does anyone have any insight into why this might occur?

Thanks
(Code is from this link http://support.microsoft.com/?scid=kb;en-us;306683

CODE

private void button5_Click(object sender, System.EventArgs e)
        {
            object oMissing = System.Reflection.Missing.Value;
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;
            oBook = oBooks.Open("c:\\book1.xls", oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            // Run the macros.
            RunMacro(oExcel, new Object[]{"DoKbTest"});
            RunMacro(oExcel, new Object[]{"DoKbTestWithParameter",
                                             "Hello from C# Client."});

            // Quit Excel and clean up.
            oBook.Close(false, oMissing, oMissing);
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook);
            oBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks);
            oBooks = null;
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel);
            oExcel = null;

            
        }    
        private void RunMacro(object oApp, object[] oRunArgs)
        {
            oApp.GetType().InvokeMember("Run",
                System.Reflection.BindingFlags.Default |
                System.Reflection.BindingFlags.InvokeMethod,
                null, oApp, oRunArgs);
        }
Helpful Member!  earthandfire (Programmer)
3 Aug 06 10:31
I've tested the following and it seems to do what you need.  I've placed comments in the code where you will need to do some additional work.

NB: This is using Excel 2000 so you may need to double check the number of oMissing parameters at each point at which they are used.

The sample Excel Macros:

CODE

Sub HelloWorld()
  MsgBox "Hello World"
End Sub

Sub HelloWorld2(WhoFrom As String)
  MsgBox "Hello World from " & WhoFrom
End Sub


Modified version of your code:

CODE

        private    object oMissing = System.Reflection.Missing.Value;

        private void button5_Click(object sender, System.EventArgs e)
        {
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;

            //.Open only needs oMissing parameters in Excel 2000
            oBook = oBooks.Open("c:\\book1.xls",
                                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            // Run the macros.
            RunMacro(oExcel, new Object[]{"HelloWorld", oMissing});
            RunMacro(oExcel, new Object[]{"HelloWorld2", "C# Client"});
            //RunMacro(oExcel, new Object[]{"DoKbTestWithParameter",
            //"Hello from C# Client."});


            // Quit Excel and clean up.
            oBook.Close(false, oMissing, oMissing);
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook);
            oBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks);
            oBooks = null;
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel);
            oExcel = null;         
        }
    
        private void RunMacro(Excel.Application  oApp, object[] oRunArgs)
        {
            //oRunArgs should have a maximum length of 31,
            //offset 0 = Macro Name

            //For simplicity this is set to require oRunArgs to have a lenngth of 2

            //0ffsets 1 to 30 = the Macros Arguments
            //some code will need to go here to check the length of oRunArgs and
            //then to replace the required oMisingParameters max params = 30

            oApp.Run(oRunArgs[0],
                oRunArgs[1], oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        }


Hope this helps.

vampirebat

piofenton (Instructor)
3 Aug 06 10:34
Thanks EarthandFire,
I will try this and let you know how it goes. Thanks for your help.
earthandfire (Programmer)
3 Aug 06 10:51
On reflection it would probably be better to create and initialise oRunArgs with 31 oMissing values.

Then modify the relevant positions in the array when you call RunMacro.

Change the RunMacro code to:

oApp.Run(oRunArgs[0],
                oRunArgs[1], oRunArgs[2], oRunArgs[3], etc., etc. ... ... oRunArgs[30]


Hope this helps.

vampirebat

piofenton (Instructor)
3 Aug 06 10:56
That makes sense. I am still having another problem though. for some inexplicable reason when I run the code on oApp.Run line I get a runtime error informing me that the HelloWorld Macro cannot be found.
This is despite it very definately being coded in the relevant  Excel file.
Any ideas why this might occur?
earthandfire (Programmer)
3 Aug 06 10:59
I found that when I added HelloWorld2 Excel placed it in Module2, as soon both were in Module1 it worked as intended.


Hope this helps.

vampirebat

piofenton (Instructor)
3 Aug 06 11:11
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Filtration.exe

Additional information: The macro 'HelloWorld2' cannot be found.

Hmmm! Still not working. It must be something I am doing. I'm using MS Excel 2003 and maybe that's where the problem lies.
earthandfire (Programmer)
3 Aug 06 11:16
Other than double-checking the number of parameters and ensuring that both macros are in the same Module, I don't know what else to suggest.

I initially used VS2003 and I've now checked it under VS2005.  Both work without a problem.


Hope this helps.

vampirebat

piofenton (Instructor)
3 Aug 06 11:18
I'll keep trying and I'll let you know if I figure out what I am missing.
Thanks very much for your help!
earthandfire (Programmer)
3 Aug 06 11:22
If I think of anything else, I'll post back.

By the way, thanks for the star.

vampirebat

piofenton (Instructor)
3 Aug 06 11:50
I sorted it out, though I have to be honest I'm not sure how I managed it. I think I may have had the Macros copied to one too many places or something! Thanks again!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close