Excel Macros from C Sharp.Net 1

[piofenton]

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

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

 

[earthandfire]

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:

Sub HelloWorld()
  MsgBox "Hello World"
End Sub

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

Modified version of your 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.

 

[piofenton]

Thanks EarthandFire,
I will try this and let you know how it goes. Thanks for your help.

 

[earthandfire]

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.

 

[piofenton]

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]

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.

 

[piofenton]

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]

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.

 

[piofenton]

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]

If I think of anything else, I'll post back.

By the way, thanks for the star.

 

[piofenton]

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!