[AccessD] C# automation of office

Hi Mark,

As JC noted you can use using statement - I usually use it this way:

using xl = Microsoft.Office.Interop.Excel;

or for MS Word

using wd = Microsoft.Office.Interop.Word;

and then

xl.Application xlApp = new xl.Application();
xl.Workbook xlWB = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

etc.

But even without using statement VS2008 intellisense does allow you to type
code much quicker than VBA/VB6 IDE does.

As for overheads of using Automation from within .NET applications - that
could be neglected - I didn't test but it could be even running
(considerably) quicker than VBA, which is interpreted p-code on runtime, and
.NET assemblies are JIT compiled into native system machine commands, and
.NET <-> COM communication was very well tuned in .NET framework...

I'd also note that some of core Microsoft.Office.Interop classes can be
inherited as base of your custom classes and you can build your own custom
Automation model(s), and that could be practical as within most of the
Automation tasks just a few Automation objects and methods are often used
(Max, don't "kill" me if you read this - I'm not proposing to build one's
own Automation models on top of Microsoft.Office.Interop I'm just noting
that such an opportunity is natively supported by .NET)...

Additionally in the coming VS2010 and .Net Framework/C# 4.0 you will not
need to write

xlWS.SaveAs(strFileName,
 Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

you will just have to write

xlWS.SaveAs(strFileName)

as it's usually done in VB6/VBA, and in VB.NET this way of coding was
available since the first versions of .NET Framework - 1.0.

All in all Office Automation development in .NET is a way more quicker and
comfortable, and smooth - I, e.g., have an experience when quite large set
of MS Excel Automation standard and class modules, which screwed MX Excel
2003 VBA (stopped to run under it without any way to make it working - even
export into text files and import back didn't work - GPF...) - that large
set was converted into .NET COM add-in within a few days, and worked
flawlessly sonce then...

Thank you.

--
Shamil


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Saturday, January 30, 2010 9:07 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] C# automation of office

Very interesting. John Walk once said he would go to his grave before coding
in VSTO.

I'm interested in knowing if there are any performance "hits" compared to
native Excel/VBA when employing dot-net interop solutions. The incredibly
increased verbosity of the coding required already speaks of lower
development speed.
>
> // 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);
>