zintech
asked on
Running scripts in C#
I have a VBA macro that I would like to be able to run in C#. I would like to have it execute and run on an Excel file. I was just wondering how I could accomplish this.
using Excel = Microsoft.Office.Interop.E xcel; //(Add Reference - COM Tab - Microsoft Excel 12.0 Object Library)
using System.Reflection; // Excel
using System.Data.OleDb; // Reading Excel
.....
Excel.Application excelApp;
Excel._Workbook excelWB;
Excel._Worksheet excelSheet;
Excel.Range oRng;
private void button1_Click(object sender, EventArgs e)
{
try
{
//Start Excel and get Application object.
excelApp = new Excel.Application();
excelApp.Visible = false;
//Get a new workbook.
excelWB = (Excel._Workbook)(excelApp .Workbooks .Add(Missi ng.Value)) ;
excelSheet = (Excel._Worksheet)excelWB. ActiveShee t;
//Add table headers going cell by cell.
excelSheet.Cells[1, 1] = "First Name";
excelSheet.Cells[1, 2] = "Last Name";
excelSheet.Cells[1, 3] = "Full Name";
excelSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
excelSheet.get_Range("A1", "D1").Font.Bold = true;
excelSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCen ter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[1, 1] = "Brown";
saNames[2, 0] = "Sue";
saNames[2, 1] = "Thomas";
saNames[3, 0] = "Jane";
saNames[3, 1] = "Jones";
saNames[4, 0] = "Adam";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
excelSheet.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = excelSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = excelSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRng = excelSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit( );
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
excelApp.Visible = false;
excelApp.UserControl = false;
excelWB.Close(true, "C:\\CSharp\\Windows\\Calc Gain\\Test 1.xlsx", false);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage , theException.Message);
errorMessage = String.Concat(errorMessage , " Line: ");
errorMessage = String.Concat(errorMessage , theException.Source);
MessageBox.Show(errorMessa ge, "Error");
}
}
HTH
Ashok
using System.Reflection; // Excel
using System.Data.OleDb; // Reading Excel
.....
Excel.Application excelApp;
Excel._Workbook excelWB;
Excel._Worksheet excelSheet;
Excel.Range oRng;
private void button1_Click(object sender, EventArgs e)
{
try
{
//Start Excel and get Application object.
excelApp = new Excel.Application();
excelApp.Visible = false;
//Get a new workbook.
excelWB = (Excel._Workbook)(excelApp
excelSheet = (Excel._Worksheet)excelWB.
//Add table headers going cell by cell.
excelSheet.Cells[1, 1] = "First Name";
excelSheet.Cells[1, 2] = "Last Name";
excelSheet.Cells[1, 3] = "Full Name";
excelSheet.Cells[1, 4] = "Salary";
//Format A1:D1 as bold, vertical alignment = center.
excelSheet.get_Range("A1",
excelSheet.get_Range("A1",
Excel.XlVAlign.xlVAlignCen
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[1, 1] = "Brown";
saNames[2, 0] = "Sue";
saNames[2, 1] = "Thomas";
saNames[3, 0] = "Jane";
saNames[3, 1] = "Jones";
saNames[4, 0] = "Adam";
saNames[4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
excelSheet.get_Range("A2",
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = excelSheet.get_Range("C2",
oRng.Formula = "=A2 & \" \" & B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = excelSheet.get_Range("D2",
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRng = excelSheet.get_Range("A1",
oRng.EntireColumn.AutoFit(
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
excelApp.Visible = false;
excelApp.UserControl = false;
excelWB.Close(true, "C:\\CSharp\\Windows\\Calc
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage
errorMessage = String.Concat(errorMessage
errorMessage = String.Concat(errorMessage
MessageBox.Show(errorMessa
}
}
HTH
Ashok
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://support.microsoft.com/kb/306683