![Page 1: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/1.jpg)
DEV390
Extending Word and Excel with Visual Studio Tools for Office
Ken GetzSenior ConsultantMCW Technologies, LLC
![Page 2: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/2.jpg)
Me.About
Senior Consultant with MCW Technologies, LLC
Microsoft Regional Director, SoCal
Technical editor for Access-VB-SQL Advisor (www.advisor.com)
Author of several developer’s books on ASP.NET, VB, Access, and VBA
ASP.NET, VB.NET, ADO.NET, VB6 and Access video training for AppDev (www.appdev.com)
www.mcwtech.comwww.developershandbook.com
![Page 3: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/3.jpg)
Agenda
Review Visual Studio Tools for Office
Calling a Web Service
Word and SQL Server
Creating an Excel Chart
Working with Menus
Using Office Controls
Language Issues
Simple Deployment Issues
![Page 4: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/4.jpg)
Visual Studio Tools for Office
Extends .NET development to Microsoft Office 2003
Facilitates smart client application development with Office 2003 and the .NET Framework
![Page 5: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/5.jpg)
Visual Studio Tools for Office
Developers using VS .NET 2003 can write code to run in reaction to Microsoft Word 2003 and Microsoft Excel 2003 events
Use Visual Basic .NET or Visual C# .NET
Code can run in Word and Excel
Not a replacement for VBA
Existing event code in VBA?Order of event handling with managed code non-deterministic
![Page 6: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/6.jpg)
Call a Web Service
Suppose you have a Web service that returns a DataSet
Need to display data in Excel
No simple solution for working with DataSet in Excel
Can easily retrieve and display data using Visual Studio Tools
Demo
![Page 7: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/7.jpg)
Set Up the Demo
Create new Excel projectAdd Web Reference
http://www.mcwtech.com/NorthwindData/Products.asmx
Rename to NorthwindData
Retrieve list of average salesFill range with results
Format range
Notice Imports statement
Demo
![Page 8: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/8.jpg)
Excel Objects
ThisApplication, ThisWorkbook
Excel.RangeOffset
CurrentRegion
AutoFormat
Font
Columns
Note use of enumerationsIntellisense doesn’t do the job
![Page 9: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/9.jpg)
How Does It Work?
Developer creates assembly, sets document properties, perhaps deploys assembly to centralized location
End user opens documentOffice uses document properties to locate and download assembly
.NET security verifies assembly is OK to run
![Page 10: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/10.jpg)
Code Loading Process
User opensdocument
CustomProps?
Trusteddoc?
Trustedcode?
New / Openevent fires
No
STOP
No
STOP
STOP
Downloadassembly
No
UnloadAppDomain
Fail
Hook upevents
Yes
Fail
Yes
CreateAppDomain Fail
Load CLRYes
Fail
![Page 11: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/11.jpg)
Word and SQL Server
Create new Word project
Add Web Reference
Create TableAdd data to cells within table
Demo
![Page 12: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/12.jpg)
Word Objects
ThisApplication, ThisDocument
Word.RangeInsertBefore, InsertParagraphAfter
Font
Word.TableCell
![Page 13: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/13.jpg)
Creating Excel Chart
Chart object in Excel completely programmable
Can embed on existing sheet, or can create separate Chart sheet
Worksheet and Chart both inherit from Sheet base class
Cast Sheet object as correct type as needed
Example creates chart on new sheet
![Page 14: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/14.jpg)
Creating a Chart
Create new chart (on its own sheet)
Use Excel macro recorder to create codeSometimes it’s just easier!
Requires cleanup and modification
Add code to reset workbookSo you can run the demo more than once
Demo
![Page 15: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/15.jpg)
Working with Menus
Office CommandBar object model required
Somewhat obtuse
Collection of menu controls of various types
Must know name of top-level menuUse tools within application to find this
Wouldn’t it be better to run sample code from menu, rather than on open?
![Page 16: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/16.jpg)
Creating Menus
Retrieve reference to top-level menu
Call Controls.Add method to add new CommandBarControl
Cast as CommandBarPopup
Call Controls.Add method to add CommandBarButton
Must remove the menu when workbook closes
Hook up Click event somehow Demo
![Page 17: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/17.jpg)
Using Office Controls
Add COM reference to MS Forms 2.0Watch out for multiple instances in registry
Select first reference
Import Microsoft.Vbe.Interop.Forms or MSForms
Depending on which instance you select
Call FindControl method (provided by template) to retrieve reference
Hook up event handler Demo
![Page 18: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/18.jpg)
Dialect Dyslexia
Normally, language doesn’t matterVisual Basic .NET is easier in this context
C# developers at a slight disadvantage
Problem areas:Parameterized properties
Optional parameters
Passing parameters (Word likes ref)
Variant members
Late binding
![Page 19: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/19.jpg)
Parameterized Properties
If a property accepts a parameterAnd the parameter isn’t an indexer
C# can’t handle it
PIAs provide accessor methods, instead:Offset becomes get_Offset
Range becomes get_Range
Value property in Excel accepts parameter
C# developers can use Value2 property
![Page 20: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/20.jpg)
Parameterized Properties
' Visual Basicrng.Offset(0, col).Value = _ dt.Columns(col).ColumnName
// C#rng.get_Offset(0, col).Value2 = dt.Columns[col].ColumnName;
![Page 21: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/21.jpg)
Optional Parameters
VBA (and VB.NET) support optional parameters
Must be at the end of the parameter list
C# does not, so must include all parameters
Can pass Type.Missing to indicate missing value
Some methods have a large number of optional parameters (up to 30 or more!)
![Page 22: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/22.jpg)
Optional Parameters' Visual Basicrng.AutoFormat( _ Excel.XlRangeAutoFormat. _ xlRangeAutoFormatColor2)
// C#rng.AutoFormat( _ Excel.XlRangeAutoFormat. _ xlRangeAutoFormatColor2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
![Page 23: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/23.jpg)
Passing Parameters
Most of Word’s methods require parameters passed by reference
VBA and VB.NET make this transparent
C# requires the ref keywordNo literal values!
![Page 24: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/24.jpg)
Passing Parameters
' Visual BasicThisDocument.Range.Delete()
// C#Object start = Type.Missing;Object end = Type.Missing;Object unit = Type.Missing;Object count = Type.Missing;ThisDocument.Range(ref start, ref end). Delete(ref unit, ref count);
![Page 25: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/25.jpg)
Variant Members
Some (not many) Word and Excel properties return/accept Variant values
C# can’t handle VariantsLook for accessor methods, or replacement properties
![Page 26: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/26.jpg)
Variant Members
' Visual Basictbl.Style = "Table Grid 8"
// C#Object style = "Table Grid 8";tbl.set_Style(ref style);
![Page 27: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/27.jpg)
Late Binding
Word dialogs use late binding (“expando interfaces”) to determine members at runtime
C# can’t support late binding
Workarounds include using Reflection to determine available members, or using the Visual Basic .NET CallByName method
No example in this demo
Doesn’t come up often
![Page 28: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/28.jpg)
Deployment Issues
Generally a simple processNeed to be aware of security issues
Policies control behavior
See separate discussion of deployment and security
What about simply handing a document and its assembly to another developer?
Even that requires some knowledge
![Page 29: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/29.jpg)
Debugging and Paths
Need to ensure that path to Excel or Word has been set correctly
Watch for path locationsCheck Project Properties|Configuration Properties|Debugging
Fix path to host application
![Page 30: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/30.jpg)
Finding the Documents
Properties window for projectAssembly Link Location
Office Document
![Page 31: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/31.jpg)
Class Names Can Kill
What if you change the default namespace for your project?
Or the default class name?
Your code won’t run
See DescriptionAttribute in class
<Assembly: DescriptionAttribute( _ "OfficeStartupClass, Version=1.0, " & _ "Class=ExcelProject1.OfficeCodeBehind")>
![Page 32: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/32.jpg)
Loading the Assembly
Creating the project adds two document properties to the Office doc
_AssemblyLocation0
_AssemblyName0
These must match reality
![Page 33: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/33.jpg)
And That’s Still Not Enough
It’s managed codeWon’t run unless you grant permissions
Visual Studio .NET sets up permissions for project in its original folder
What if you move the project?Or hand the project to another developer?
What if you want to run this session’s samples?
![Page 34: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/34.jpg)
Create a Policy
Add a code access groupCreate a new policy?
Easiest, for development purposes, to allow all code within a folder to run
Specify c:\path\* as the location
Specify “FullTrust”
This isn’t a good idea on users’ machinesWorks fine for development
![Page 35: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/35.jpg)
But Wait, There’s More!
Just barely scratched the surface
Word/Excel object models are hugeProvide a ton of useful functionality
Language issues?C# users will want to create “wrapper” classes
What about no-touch deployment?Granting permissions for download code?
Requires an entire separate session
![Page 36: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/36.jpg)
Next StepsNext StepsGet Visual Studio Tools for Office
msdn.microsoft.com/vstudio/office
Install Visual Studio .NET 2003 and then full installation of Office 2003
Try out some of the walkthroughs/demos
Take existing Office automation code and try recreating in managed code
![Page 37: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/37.jpg)
Community ResourcesCommunity ResourcesWhite papers:
Visual Studio Tools for Office: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/VSTOIntro.asp?frame=true
Introduction to the Word Object Model: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/WordObject.asp?frame=true
Introduction to the Excel Object Model: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp?frame=true
![Page 38: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/38.jpg)
Ask The ExpertsGet Your Questions Answered
Stop by Ask the Experts area16:00 to 17:00, Wednesday
![Page 39: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/39.jpg)
Community Resources
Community Resourceshttp://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)http://www.mvp.support.microsoft.com/
NewsgroupsConverse online with Microsoft Newsgroups, including Worldwidehttp://www.microsoft.com/communities/newsgroups/default.mspx
User GroupsMeet and learn with your peershttp://www.microsoft.com/communities/usergroups/default.mspx
![Page 40: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/40.jpg)
evaluationsevaluations
![Page 41: DEV390 Extending Word and Excel with Visual Studio Tools for Office Ken Getz Senior Consultant MCW Technologies, LLC](https://reader035.vdocuments.us/reader035/viewer/2022062304/56649e495503460f94b3d002/html5/thumbnails/41.jpg)
© 2003 Microsoft Corporation. All rights reserved.© 2003 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.