i have the following macro
Sub test()
Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Worksheets
xsheet.Select
With xsheet.UsedRange
.Value = .Value
End With
Next xsheet
End Sub
Is there a way to add it to excel file and to execute it using c# ?
Any help would be the most appreciated .
1) Here is the code I use that works for Excel using the .Net reference: Microsoft.Office.Interop.Excel v14 (not the ActiveX COM Reference):
using System;
using Microsoft.Office.Interop.Excel;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
RunVBATest();
}
public static void RunVBATest()
{
Application oExcel = new Application();
oExcel.Visible = true;
Workbooks oBooks = oExcel.Workbooks;
_Workbook oBook = oBooks.Open("C:\\temp\\Book1.xlsm");
// Run the macro.
RunMacro(oExcel, new Object[] { "TestMsg" });
//Run a macro with parameters
//RunMacro(oExcel, new Object[] { "ShowMsg", "Hello from C# Client", "Demo to run Excel macros from C#" });
// Quit Excel and clean up
oBook.Saved = true;
oBook.Close(false);
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
}
private static void RunMacro(object oApp, object[] oRunArgs)
{
oApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod,
null, oApp, oRunArgs);
}
}
}
}
2) make sure you put the Macro code in a Module (a Global BAS file)..
Public Sub TestMsg()
MsgBox ("Hello Stackoverflow")
End Sub
3) make sure you enable Macro Security and Trust access to the VBA Project object model:
Solution by Siddharth Rout extracted from this MSDN issue
This should work :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//~~> Define your Excel Objects
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
//~~> Start Excel and open the workbook.
xlWorkBook = xlApp.Workbooks.Open("E:\\Users\\Siddharth Rout\\Desktop\\book1.xlsm");
//~~> Run the macros by supplying the necessary arguments
xlApp.Run("test");
//~~> Clean-up: Close the workbook
xlWorkBook.Close(false);
//~~> Quit the Excel Application
xlApp.Quit();
//~~> Clean Up
releaseObject(xlApp);
releaseObject(xlWorkBook);
}
//~~> Release the objects
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
}
If your macro has arguments, lets say :
Sub ShowMsg(msg As String, title As String)
MsgBox msg, vbInformation, title
End Sub
You have to change xlApp.Run("test"); to xlApp.Run("ShowMsg", "Hello from C# Client", "Demo to run Excel macros from C#");
