Link to home
Start Free TrialLog in
Avatar of zintech
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.
Avatar of santhimurthyd
santhimurthyd
Flag of United States of America image

using Excel = Microsoft.Office.Interop.Excel;  //(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(Missing.Value));
                excelSheet = (Excel._Worksheet)excelWB.ActiveSheet;

                //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.xlVAlignCenter;

                // 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\\CalcGain\\Test1.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(errorMessage, "Error");
            }
        }

HTH
Ashok
ASKER CERTIFIED SOLUTION
Avatar of Ashok
Ashok
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial