[dba-VB] [AccessD] C# automation of office

Hello John,

Sorry this is late in coming,

the following code is take from a live project of mine where we write to
Excel from VS2008.  I have snipped sections of code from one routine, and I
have highlighted with my comments the five seperate bits I have given you,
each bit is only a few lines of code, but they may be all you need to get
Excel talking.  I hope the code formats ok here, if not, let me know and I
will send you a text file.

Note a few things below
1) you can insert comments easily
2) you can insert columns easily
3) selecting a range if often the first step to performing an action

HINT:  Record a macro in Excel to add color and then you will have the code
to do that from within C#

thanks

Mark





// This bit shows the using statements you will need

using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;




// This bit shows how to op an connection to Excel

            if (grdParts.RowCount > 0)
            {
                if (MessageBox.Show("Export Master Parts List?", "Export",
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == (DialogResult.Yes))
                {
                    this.Cursor = Cursors.WaitCursor;
                    Int32 intPos = bsParts.Position;
                    try
                    {
                        if (SaveChanges())
                        {
                            Microsoft.Office.Interop.Excel.Application xlApp
= new Microsoft.Office.Interop.Excel.Application();
                            Microsoft.Office.Interop.Excel.Workbook xlWB =
xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                            Microsoft.Office.Interop.Excel.Worksheet xlWS =
(Microsoft.Office.Interop.Excel.Worksheet)xlWB.Worksheets[1];

                            xlApp.Visible = true;

                            xlWS.Cells[2, 6] = "Parts List ";
                            xlWS.Cells[3, 6] = "Date " +
DateTime.Now.ToLongDateString();


// This bit shows how to write comments to excel
                            Microsoft.Office.Interop.Excel.Range nRange;

                            nRange =
(Microsoft.Office.Interop.Excel.Range)xlWS.Cells[intRowIndex +
intRowIncrement, intColumnIndex - 11];
                            nRange.AddComment(strFullComments.Replace("\r",
""));
                            nRange.Comment.Shape.TextFrame.AutoSize = true;




// This but shows  how to insert a column
                            Range rng = (Range)xlWS.Cells[1,2];
                            Range column = rng.EntireColumn;

 column.Insert(XlInsertShiftDirection.xlShiftToRight, false);

 column.Insert(XlInsertShiftDirection.xlShiftToRight, false);


// this bit shows how to save the sheet
                            String strFileName = "C:\\McAfee Prd Report_" +
DateTime.Now.ToLongDateString() + "-" +
DateTime.Now.ToLongTimeString().Replace(":", "") + ".xls";
                            xlWS.SaveAs(strFileName, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);






On 22 January 2010 18:10, jwcolby <jwcolby at colbyconsulting.com> wrote:

> Does anyone have a good information source for automating office
> (specifically Excel) from C#?
>
> I have a spreadsheet which has code inside of it which pulls data out of
> SQL Server and dumps it
> into pages of the spreadsheet.  I WAS using Access to do this but my Office
> 2007 install broke my
> forms (event properties no longer wire to the code behind form).  Thus from
> C# I need to cause a
> spreadsheet to open, call a method of the code and pass a server / database
> string to it.
>
> Any help will be greatly appreciated.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>