[AccessD] C# automation of office

Mark Simms marksimms at verizon.net
Sun Jan 31 09:13:19 CST 2010


Very interesting and thanks much for the lesson.
(Wow, it took MSFT over 5 years to finally handle optional parameters
properly !?)

Final questions: deployment, referencing
When you deploy a dot-net Office add-in, is it a single COM DLL then ?
In other words, all the users need is the dot-net 3.0 framework installed.

Referencing to the DLL from the Excel/Word/Powerpoint instance: how is that
done ?
The regular add-in menu or a custom-install is required ?

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Shamil Salakhetdinov
> Sent: Saturday, January 30, 2010 3:10 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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);
> >
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>






More information about the AccessD mailing list