advertisement

ILUG 2007 - Notes and Office Integration

50 %
50 %
advertisement
Information about ILUG 2007 - Notes and Office Integration

Published on May 24, 2007

Author: johnhead

Source: slideshare.net

Description

The Magic of Integration and the revealing of what's behind the curtain : Microsoft Office and IBM Lotus Notes and Domino integration. Microsoft Office 2007 and Lotus Notes 8 will bring a new level of integration possibilities to the Lotus Notes and Domino world. This session will look at the new functionality in Office 2007, such as the Office Ribbon and OpenXML file formats. We will discuss the impact on Mail Merge, data transfer, and how this will impact your applications and development.

We will also look at the updated Visual Studio Tools for Office 2007. This session will also look at Lotus Notes 8 and it's impact on integration. The session starts at the beginning ... and then dives deep into the integration possiblities.

While this sesison is mostly about the code that makes it work, users and managers will get detailed sample applications to take home and put to use immediately! In the end, this session will help you add value to your applications not possible without the magic of integration. Presented by Rocky Oliver for John Head
advertisement

The Magic of Integration and the revealing of what's behind the curtain : Microsoft Office and IBM Lotus Notes and Domino integration John D. Head PSC Group, LLC

Before we begin … Please turn off/set to vibrate/mute all Cell Phones Pagers Computers Please remember to fill out your evaluations

Please turn off/set to vibrate/mute all Cell Phones Pagers Computers Please remember to fill out your evaluations

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Who is John D. Head? Framework Manager at PSC Group, LLC Involved in Lotus technology since 1993 Speaker for over 25 sessions at Lotusphere since 1996 Speaker at Advisor and Lotus Developer conferences IBM Lotus Notes/Domino Certified Author for Advisor Magazine and other publications on Office and SmartSuite integration with Notes LotusUserGroup.org contributing Author and Forum moderator Blog with articles www.johndavidhead.com

Framework Manager at PSC Group, LLC

Involved in Lotus technology since 1993

Speaker for over 25 sessions at Lotusphere since 1996

Speaker at Advisor and Lotus Developer conferences

IBM Lotus Notes/Domino Certified

Author for Advisor Magazine and other publications on Office and SmartSuite integration with Notes

LotusUserGroup.org contributing Author and Forum moderator

Blog with articles

www.johndavidhead.com

PSC Group, LLC IBM Premium Business Partner for 15+ Years Microsoft Gold Partner Winner 2007 Lotus Award Winner 2007 IBM Beacon Award Notes 8 Design Partner Host of OpenNTF.org Host of the following blogs: Mike Rhodin – Lotus GM (www.mikerhodin.com) Ed Brill’s ( www.edbrill.com ) Alan Lepofsky’s Notes Tip’s ( www.alanlepofsky.net ) Alan Gartenberg - Sametime ( www.adamgartenberg.com ) Jeff Eisen – Hannover ( www.jeffeisen.com ) Domino Server Team ( www.dominoblog.com ) Chris Pepin – IBM CTO’s Office (www.chrispepin.com) www.PSCListens.com

IBM Premium Business Partner for 15+ Years

Microsoft Gold Partner

Winner 2007 Lotus Award

Winner 2007 IBM Beacon Award

Notes 8 Design Partner

Host of OpenNTF.org

Host of the following blogs:

Mike Rhodin – Lotus GM (www.mikerhodin.com)

Ed Brill’s ( www.edbrill.com )

Alan Lepofsky’s Notes Tip’s ( www.alanlepofsky.net )

Alan Gartenberg - Sametime ( www.adamgartenberg.com )

Jeff Eisen – Hannover ( www.jeffeisen.com )

Domino Server Team ( www.dominoblog.com )

Chris Pepin – IBM CTO’s Office (www.chrispepin.com)

A Quick Poll Who is using … Office 97 and earlier Office 2000 Office XP Office 2003 Office 2007 OpenOffice.org or StarOffice Office for the Mac Microsoft Works iWork or AppleWorks SmartSuite Other

Who is using …

Office 97 and earlier

Office 2000

Office XP

Office 2003

Office 2007

OpenOffice.org or StarOffice

Office for the Mac

Microsoft Works

iWork or AppleWorks

SmartSuite

Other

The Demo Databases Session example database Single database with all examples You can configure it for your Notes client Example numbers in the presentation match those in the database Lotusphere 2007 session database Included on the conference CD Updated download available Includes design elements for this session Personal name and address book My demos work with the standard Notes 6, 7 and 8 templates

Session example database

Single database with all examples

You can configure it for your Notes client

Example numbers in the presentation match those in the database

Lotusphere 2007 session database

Included on the conference CD

Updated download available

Includes design elements for this session

Personal name and address book

My demos work with the standard Notes 6, 7 and 8 templates

Integration History 1994 DDE with Ami Pro 1996 Notes R4 OLE LotusScript VBA 1997 SmartSuite with LotusScript (Word Pro) 1998 Notes R5 with COM support 2001 Notes Domino 6 2004 Notes Domino 6.5 with LS2J and DXL 2005 Notes Domino 7 with web services 2007 2007 Office System Lotus Notes/Domino 8

1994

DDE with Ami Pro

1996

Notes R4

OLE

LotusScript

VBA

1997

SmartSuite with LotusScript (Word Pro)

1998

Notes R5 with COM support

2001

Notes Domino 6

2004

Notes Domino 6.5 with LS2J and DXL

2005

Notes Domino 7 with web services

2007

2007 Office System

Lotus Notes/Domino 8

The Good News We are at the end of the Renaissance of the Integration Era In the “Medieval Times” DDE with Ami Pro NotesSQL was used to get even the basic of data OLE embedding Notes F/X The Renaissance brought us new tools OLE automation COM automation XML Web Services

We are at the end of the Renaissance of the Integration Era

In the “Medieval Times”

DDE with Ami Pro

NotesSQL was used to get even the basic of data

OLE embedding

Notes F/X

The Renaissance brought us new tools

OLE automation

COM automation

XML

Web Services

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Definitions for Notes Programmers Let’s go over a few words you need to know COM OLE

Let’s go over a few words you need to know

COM

OLE

What Is COM? Component Object Model Microsoft standard Defines the guidelines for objects Objects must tell all other programs what is supported Properties Methods Objects Consistent, programmatic access to functionality

Component Object Model

Microsoft standard

Defines the guidelines for objects

Objects must tell all other programs what is supported

Properties

Methods

Objects

Consistent, programmatic access to functionality

What Is OLE? Object linking and embedding Another Microsoft standard Allows one application document to include a portion of another application A Word document with an Excel spreadsheet inside OLE/1 — attachments OLE/2 — linking or embedding

Object linking and embedding

Another Microsoft standard

Allows one application document to include a portion of another application

A Word document with an Excel spreadsheet inside

OLE/1 — attachments

OLE/2 — linking or embedding

OLE/1 — File Attachments Advantages Functionality clearly divided between applications No memory issues Much smaller size footprint Enables template usage Disadvantages Requires more planning More effort for basic functionality Attachment does not know about the Notes object Must manually connect and update Notes 6 added the ability to round-trip edit attachments

Advantages

Functionality clearly divided between applications

No memory issues

Much smaller size footprint

Enables template usage

Disadvantages

Requires more planning

More effort for basic functionality

Attachment does not know about the Notes object

Must manually connect and update

Notes 6 added the ability to round-trip edit attachments

OLE/2 — Linking and Embedding Advantages Integrated — single interface when using embedded objects (when used in-line) Document containing objects is “aware” of the embedded object Less work to get object open for the user

Advantages

Integrated — single interface when using embedded objects (when used in-line)

Document containing objects is “aware” of the embedded object

Less work to get object open for the user

OLE/2 — Linking and Embedding (cont.) Disadvantages OLE embedded objects are Much bigger … 10 to 15 times Difficult for users when the object is more than one page OLE embedding was not reliable before Office XP with Windows XP File menu of container application is never updated #1 Issue … no clean way to export data from embedded objects “ Locked” data is bad

Disadvantages

OLE embedded objects are

Much bigger … 10 to 15 times

Difficult for users when the object is more than one page

OLE embedding was not reliable before Office XP with Windows XP

File menu of container application is never updated

#1 Issue … no clean way to export data from embedded objects

“ Locked” data is bad

Early Binding vs. Late Binding Early binding is when you declare what an object variable will be before you actually assign the object to it Dim db as NotesDatabase Set db = session.currentdatabase Late binding is when you assign an object to a variable without creating the object variable before hand Set db = New NotesDatabase(“server”, “names.nsf”) Early binding is much more efficient than late binding

Early binding is when you declare what an object variable will be before you actually assign the object to it

Dim db as NotesDatabase

Set db = session.currentdatabase

Late binding is when you assign an object to a variable without creating the object variable before hand

Set db = New NotesDatabase(“server”, “names.nsf”)

Early binding is much more efficient than late binding

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Integration From Notes From Notes, you will write code to manipulate the application In most cases, you will be converting Visual Basic for Applications (VBA) code to LotusScript The VBA code will be written so it saves in Notes as LotusScript Once you get the knack of converting the code, you can focus on the process vs. the code

From Notes, you will write code to manipulate the application

In most cases, you will be converting Visual Basic for Applications (VBA) code to LotusScript

The VBA code will be written so it saves in Notes as LotusScript

Once you get the knack of converting the code, you can focus on the process vs. the code

Demo 01 Creating a Word Document — “Hello World”

Creating a Word Document Create an instance of the application object All applications that support COM have object names To find the object name Review the application documentation Look it up in the Windows registry Never use version-specific application object names Good: Word.application Bad: Word.application.11 When the application upgrades, you must modify your code Set hwd = CreateObject("Word.Application") Variant Application Object Name LotusScript Method

Create an instance of the application object

All applications that support COM have object names

To find the object name

Review the application documentation

Look it up in the Windows registry

Never use version-specific application object names

Good: Word.application

Bad: Word.application.11

When the application upgrades, you must modify your code

Creating a Word Document (cont.) You must follow the applications object model: Word.application Documents Document Paragraph Bookmark CustomDocumentProperties If accessing the current open document, use Application.ActiveDocument Application.ActiveWorkbook Application.ActivePresentation Etc. All applications have an object map in help

You must follow the applications object model:

Word.application

Documents

Document

Paragraph

Bookmark

CustomDocumentProperties

If accessing the current open document, use

Application.ActiveDocument

Application.ActiveWorkbook

Application.ActivePresentation

Etc.

All applications have an object map in help

The Easiest Way to Start Microsoft Office provides us with a recorder Records all keyboard actions Mouse actions are sometimes recorded, so avoid when possible Generates VBA code for you Easy way to learn how something is done Table is created Page settings are set Etc.

Microsoft Office provides us with a recorder

Records all keyboard actions

Mouse actions are sometimes recorded, so avoid when possible

Generates VBA code for you

Easy way to learn how something is done

Table is created

Page settings are set

Etc.

What’s up with the Recorded Syntax? This is the recorded syntax: Saving LotusScript with wdToggle and wdAlignParagraphCenter fails! Need to replace VBA constants with raw values Write a macro Msgbox [constantname] Or … Selection.TypeText Text:="this is new text" Selection.TypeParagraph Selection.TypeParagraph Selection.Font.Bold = wdToggle Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter Selection.TypeText Text:="this is more text"

This is the recorded syntax:

Saving LotusScript with wdToggle and wdAlignParagraphCenter fails!

Need to replace VBA constants with raw values

Write a macro

Msgbox [constantname]

Or …

Use the Microsoft Constants Database Provides a listing of all Microsoft Office variables and their converted constants Extraction of constants by John Collidge Database built by Tom Duff Now an OpenNTF.org project Supports Microsoft Office 2000, XP, 2003, and 2007 More features planned!

Provides a listing of all Microsoft Office variables and their converted constants

Extraction of constants by John Collidge

Database built by Tom Duff

Now an OpenNTF.org project

Supports Microsoft Office 2000, XP, 2003, and 2007

More features planned!

Domino COM Basics Working with the Domino Objects is almost the same as writing code in Notes! Only supports backend objects No NotesUIDocument, NotesUIView, NotesUIWorkspace, etc. COM support was added in Notes 5.0.2b Updated in 6.5.1 to support 6.X ND7 COM support out of the box COM interface is the same as LotusScript, with some minor exceptions

Working with the Domino Objects is almost the same as writing code in Notes!

Only supports backend objects

No NotesUIDocument, NotesUIView, NotesUIWorkspace, etc.

COM support was added in Notes 5.0.2b

Updated in 6.5.1 to support 6.X

ND7 COM support out of the box

COM interface is the same as LotusScript, with some minor exceptions

Registering the Notes COM Objects Sometimes Notes does not register itself properly with the Windows registry From the Windows run line Regsvr32 “c:program fileslotus otes lsxbe.dll” From a Notes agent Shell(|regsvr32 “c:program fileslotus otes lsxbe.dll|)

Sometimes Notes does not register itself properly with the Windows registry

From the Windows run line

Regsvr32 “c:program fileslotus otes lsxbe.dll”

From a Notes agent

Shell(|regsvr32 “c:program fileslotus otes lsxbe.dll|)

Domino COM Basics Before you start writing VBA code, you must load the Domino COM objects In MS Office  Tools  References

Before you start writing VBA code, you must load the Domino COM objects

In MS Office  Tools  References

Domino COM Basics (cont.) The COM objects must be able to find the Notes.ini Looks in: Notes program directory Path KeyFileName in Notes.ini is used to determine what ID to use for authentication Multiple installed version of Notes is a problem Uses the last installed version HKEY_CLASSES_ROOT.nsfShellOpenCommand HKEY_CLASSES_ROOT otes.exeShellOpenCommand HKEY_LOCAL_MACHINESOFTWARELotusNotes

The COM objects must be able to find the Notes.ini

Looks in:

Notes program directory

Path

KeyFileName in Notes.ini is used to determine what ID to use for authentication

Multiple installed version of Notes is a problem

Uses the last installed version

HKEY_CLASSES_ROOT.nsfShellOpenCommand

HKEY_CLASSES_ROOT otes.exeShellOpenCommand

HKEY_LOCAL_MACHINESOFTWARELotusNotes

Make Your End Users Happy! Prevent password prompts by … Notes 5.X Notes 6 and 7

Prevent password prompts by …

Domino COM Basics Must use “NotesSession.Initialize("" ) ” to establish connection between application and Notes Can not use extended syntax (i.e., dot notation) to reference items in a document NO NotesDocument.Form(0) YES NotesDocument.GetItemValue(“Form ”)(0)

Must use “NotesSession.Initialize("" ) ” to establish connection between application and Notes

Can not use extended syntax (i.e., dot notation) to reference items in a document

NO

NotesDocument.Form(0)

YES

NotesDocument.GetItemValue(“Form ”)(0)

Domino COM Basics Can not use “New” to create Domino objects Exception is NotesSession NO Dim db as New NotesDatabase(server, dbpath) YES Dim db as NotesDatabase Set db = NotesSession.GetDatabase(server, path)

Can not use “New” to create Domino objects

Exception is NotesSession

NO

Dim db as New NotesDatabase(server, dbpath)

YES

Dim db as NotesDatabase

Set db = NotesSession.GetDatabase(server, path)

Domino COM Basics No such thing as a “current” environment or object No CurrentDatabase, DocumentContext, etc. If a property returns an array of objects, you must assign the property to a variable first NO Dim view as NotesView Set view = NotesDatabase.Views(0) YES Dim views as Variant, view as NotesView views = NotesDatabase.Views Set view = views(0)

No such thing as a “current” environment or object

No CurrentDatabase, DocumentContext, etc.

If a property returns an array of objects, you must assign the property to a variable first

NO

Dim view as NotesView

Set view = NotesDatabase.Views(0)

YES

Dim views as Variant, view as NotesView

views = NotesDatabase.Views

Set view = views(0)

Domino COM Basics VB doesn’t use Forall…EndAll Uses For Each…Next instead VB doesn’t support Today keyword VB doesn’t support using pipes ( | ) and braces ( {} ) for string delimiting Refer to ND Designer Help for more information

VB doesn’t use Forall…EndAll

Uses For Each…Next instead

VB doesn’t support Today keyword

VB doesn’t support using pipes ( | ) and braces ( {} ) for string delimiting

Refer to ND Designer Help for more information

Demo 02 Word Template — Data From Notes

Managing the Template How did I get that template in my new dialog? Template installation tool Each template is a document Supports all Office 2000 thru 2007 applications Can also be used as a programmatic template tool Located on OpenNTF.org

How did I get that template in my new dialog?

Template installation tool

Each template is a document

Supports all Office 2000 thru 2007 applications

Can also be used as a programmatic template tool

Located on OpenNTF.org

Merging Mail From Notes Most requested Notes to Office integration functionality Users have no idea how to get data from Notes into Word Lotus has not provided this functionality … yet Quick win for you — take this code back and add it to your boss’s personal name and address book and be the HERO!

Most requested Notes to Office integration functionality

Users have no idea how to get data from Notes into Word

Lotus has not provided this functionality … yet

Quick win for you — take this code back and add it to your boss’s personal name and address book and be the HERO!

Mail Merge in Different Versions of Word Mail merge is version specific One version for Word 2000 and before One version for Word XP One version for Word 2003 Minor change on closing documents in 2003 New version for Word 2007

Mail merge is version specific

One version for Word 2000 and before

One version for Word XP

One version for Word 2003

Minor change on closing documents in 2003

New version for Word 2007

Use the Mail Merge Wizard to Your Advantage The Mail Merge Wizard has six steps 1. Select document type 2. Select starting document 3. Select recipients 4. Write your letter 5. Preview your letters 6. Complete the merge You can control which: Wizard steps display Step to start on

The Mail Merge Wizard has six steps

1. Select document type

2. Select starting document

3. Select recipients

4. Write your letter

5. Preview your letters

6. Complete the merge

You can control which:

Wizard steps display

Step to start on

Demo 03 Mail Merge From Address Book

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Integration with Microsoft Excel Working with Excel is very different than Word The objects we use are different In Excel, we tend to move more data from Notes to the application Lots of rows in Excel vs. filling in bookmarks in Word Microsoft Word Microsoft Excel Text Position/Cursor Cell Paragraph Range Page Worksheet Document Workbook

Working with Excel is very different than Word

The objects we use are different

In Excel, we tend to move more data from Notes to the application

Lots of rows in Excel vs. filling in bookmarks in Word

The Beginning with Excel To start, use a different object Set hwd = CreateObject("Excel.Application") Next, create a new workbook hwd.Workbooks.Add And it’s time to add data

To start, use a different object

Set hwd = CreateObject("Excel.Application")

Next, create a new workbook

hwd.Workbooks.Add

And it’s time to add data

Working with Cells and Ranges We can work with cells or ranges For operating on a specific cell … hwd.cells(1,1).value=“Name” hwd.cells(1,2).value=“Category” For operating on multiple cells … use a range xlRange="$A$1:$C$1“ With hwd .Range(xlRange).Font.Bold = True ' set row to bold .Range(xlRange).Borders.LineStyle=1' set border .Range(xlRange).Borders.Weight=1 ' set weight of border End With

We can work with cells or ranges

For operating on a specific cell … hwd.cells(1,1).value=“Name”

hwd.cells(1,2).value=“Category”

For operating on multiple cells … use a range xlRange="$A$1:$C$1“

With hwd

.Range(xlRange).Font.Bold = True ' set row to bold

.Range(xlRange).Borders.LineStyle=1' set border

.Range(xlRange).Borders.Weight=1 ' set weight of border

End With

Dealing with the Default Row and Column Size Excel does not autosize columns and rows Do it within your code hwd.Range(xlRange).EntireColumn.AutoFit or hwd.Range(xlRange).EntireRow.AutoFit

Excel does not autosize columns and rows

Do it within your code hwd.Range(xlRange).EntireColumn.AutoFit or hwd.Range(xlRange).EntireRow.AutoFit

Creating a Chart xlRange="$A$1:$C$10" hwd.Charts.Add hwd.ActiveChart.ChartType = 51 hwd.ActiveChart.SetSourceData hwd.Sheets("Sheet1").Range("A1:C10"), 2 hwd.ActiveChart.SeriesCollection(1).Name = "=""Notes Chart""" hwd.ActiveChart.Location 2, "Sheet1“ With hwd.ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Notes to Excel Demo“ .Axes(1,1).HasTitle = True .Axes(1,1).AxisTitle.Characters.Text = "X Axis“ .Axes(2,1).HasTitle = True .Axes(2,1).AxisTitle.Characters.Text = "Y Axis“ .ApplyDataLabels 2, True .HasDataTable = False End With hwd.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.46, 0,0

xlRange="$A$1:$C$10"

hwd.Charts.Add

hwd.ActiveChart.ChartType = 51

hwd.ActiveChart.SetSourceData hwd.Sheets("Sheet1").Range("A1:C10"), 2

hwd.ActiveChart.SeriesCollection(1).Name = "=""Notes Chart"""

hwd.ActiveChart.Location 2, "Sheet1“

With hwd.ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "Notes to Excel Demo“

.Axes(1,1).HasTitle = True

.Axes(1,1).AxisTitle.Characters.Text = "X Axis“

.Axes(2,1).HasTitle = True

.Axes(2,1).AxisTitle.Characters.Text = "Y Axis“

.ApplyDataLabels 2, True

.HasDataTable = False

End With

hwd.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.46, 0,0

Demo 04 Working with Microsoft Excel

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Integration with Microsoft PowerPoint PowerPoint is closer to Word than Excel Microsoft Word Microsoft PowerPoint Text Position/Cursor Text Position/Cursor Paragraph Text Block Page Slide Document Presentation

PowerPoint is closer to Word than Excel

The Beginning with PowerPoint To start, use the PowerPoint object Set hwd = CreateObject(“PowerPoint.Application") Next, create a new presentation hwd.Presentation.Add 1, 1 And it’s time to add data

To start, use the PowerPoint object

Set hwd = CreateObject(“PowerPoint.Application")

Next, create a new presentation

hwd.Presentation.Add 1, 1

And it’s time to add data

Working with PowerPoint Shapes Every slide has shapes Title text box Bullet text box Etc. Each shape has a name Finding the name is typically “hunt and peck” I always use the macro function to determine the shape name You can also write a macro

Every slide has shapes

Title text box

Bullet text box

Etc.

Each shape has a name

Finding the name is typically “hunt and peck”

I always use the macro function to determine the shape name

You can also write a macro

Working with Shapes You have to navigate to enter text in PowerPoint Shape TextFrame Characters Selection hwd.ActiveWindow.Selection.SlideRange.Shapes("Rectangle 2").Select hwd.ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select hwd.ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(1,0).Select With hwd.ActiveWindow.Selection.TextRange .Text = sessiontitle End With

You have to navigate to enter text in PowerPoint

Shape

TextFrame

Characters

Selection

Demo 05 Working with PowerPoint

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Third Party Solutions

More More More

Q & A

Automation From the Microsoft Side Many integration scenarios require that user interaction start or solely be from an application other than Notes Many times we will not have the Notes client installed We will not be writing LotusScript We may call LotusScript thru the Notes COM interface We need to take advantages of “new” technologies XML Web services .NET Managed code

Many integration scenarios require that user interaction start or solely be from an application other than Notes

Many times we will not have the Notes client installed

We will not be writing LotusScript

We may call LotusScript thru the Notes COM interface

We need to take advantages of “new” technologies

XML

Web services

.NET

Managed code

About Web Services Self-contained application published and invoked from the Web Based on XML Uses WSDL and SOAP Designed for machine-to-machine interaction Advantages Connects various applications running on different platforms Uses open standards and protocols Uses HTTP, bypassing firewall and other security measures Disadvantages Poor performance as opposed to DCOM (Distributed COM) Requires an internet connection for both the client and the server

Self-contained application published and invoked from the Web

Based on XML

Uses WSDL and SOAP

Designed for machine-to-machine interaction

Advantages

Connects various applications running on different platforms

Uses open standards and protocols

Uses HTTP, bypassing firewall and other security measures

Disadvantages

Poor performance as opposed to DCOM (Distributed COM)

Requires an internet connection for both the client and the server

Calling a Web Service from Microsoft Word To call a Web service from Word (or other Office application), use the following code: SWSDL = "http://www.johndavidhead.com/advisorsession042006.nsf/SessionInfo?wsdl" Set Client = CreateObject("MSSOAP.SoapClient") Call Client.mssoapinit(sWSDL) GetSessionList = Client.ListAllSessions This code uses the SOAPClient control that is part of Windows and Internet Explorer SOAP is a way to package and interact with a Web service

To call a Web service from Word (or other Office application), use the following code:

SWSDL = "http://www.johndavidhead.com/advisorsession042006.nsf/SessionInfo?wsdl"

Set Client = CreateObject("MSSOAP.SoapClient")

Call Client.mssoapinit(sWSDL)

GetSessionList = Client.ListAllSessions

This code uses the SOAPClient control that is part of Windows and Internet Explorer

SOAP is a way to package and interact with a Web service

More Detailed Web Services with Word Now, we want to take something the user selected and get more info about it This is a second method in the Web service Public Function GetSessionFieldValue(sessionid, fieldName) As String sWSDL = "http://www.johndavidhead.com/advisorsession042006.nsf/SessionInfo?wsdl" Set Client = CreateObject("MSSOAP.SoapClient") Call Client.mssoapinit(sWSDL) GetSessionFieldValue = Client.GetSessionInfo(sessionid, fieldName) End Function Sessionid and fieldname are values we pass into the Web service The return value comes back as string

Now, we want to take something the user selected and get more info about it

This is a second method in the Web service

Public Function GetSessionFieldValue(sessionid, fieldName) As String

sWSDL = "http://www.johndavidhead.com/advisorsession042006.nsf/SessionInfo?wsdl"

Set Client = CreateObject("MSSOAP.SoapClient")

Call Client.mssoapinit(sWSDL)

GetSessionFieldValue = Client.GetSessionInfo(sessionid, fieldName)

End Function

Sessionid and fieldname are values we pass into the Web service

The return value comes back as string

Demo Preview - Word 2007 and Web Services Same demo as the Word 2007 COM demo from the previous session Different technology Get to the same place with different technique Code written inside the Visual Basic for Applications IDE

Same demo as the Word 2007 COM demo from the previous session

Different technology

Get to the same place with different technique

Code written inside the Visual Basic for Applications IDE

Demo 06 Word 2007 Demo — Web Services

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Office and XML Details Office 2003 added XML support Word and Excel Export-like Save option Excel can import data from an XML file Import XML schema Transform XML with XML style sheets

Office 2003 added XML support

Word and Excel

Export-like Save option

Excel can import data from an XML file

Import XML schema

Transform XML with XML style sheets

Office and XML (cont.) Office 2007 uses XML as the default new file format Microsoft is working to make the new XML file format an ISO standard Called Open XML Office 2003 can open these new formats with an update you can download from Microsoft Manipulating the Open XML file directly is the lowest level of automating Office Developers have been requesting this functionality for years Extreme level of difficulty Heavily documented Real-world samples lacking

Office 2007 uses XML as the default new file format

Microsoft is working to make the new XML file format an ISO standard

Called Open XML

Office 2003 can open these new formats with an update you can download from Microsoft

Manipulating the Open XML file directly is the lowest level of automating Office

Developers have been requesting this functionality for years

Extreme level of difficulty

Heavily documented

Real-world samples lacking

Working with Open XML The Office 2007 file formats are compressed directories Think a “zip” file You can view the directory by renaming the file extension to .zip The easier way to view the file in native XML is through Package Explorer Developed by Wouter Van Vugt Download from Codeplex (http://www.codeplex.com/PackageExplorer)

The Office 2007 file formats are compressed directories

Think a “zip” file

You can view the directory by renaming the file extension to .zip

The easier way to view the file in native XML is through Package Explorer

Developed by Wouter Van Vugt

Download from Codeplex

(http://www.codeplex.com/PackageExplorer)

Editing Open XML with Package Explorer Browse and edit Office Open XML packages Edit XML parts in an XML aware editor Validate parts with the ECMA (standards board) final schemas Edit Dublin Core properties directly in a property grid Improved usability (dirty bit management) Docking UI

Browse and edit Office Open XML packages

Edit XML parts in an XML aware editor

Validate parts with the ECMA (standards board) final schemas

Edit Dublin Core properties directly in a property grid

Improved usability (dirty bit management)

Docking UI

Working with Domino Data as XML Use the core Domino classes LotusScript and Java Domino XML (DXL) Cheat  Add passthru HTML to a form Build your own agent to output XML

Use the core Domino classes

LotusScript and Java

Domino XML (DXL)

Cheat 

Add passthru HTML to a form

Build your own agent to output XML

A Domino Agent That Creates XML Dim s As New NotesSession Dim db As NotesDatabase Dim doc As NotesDocument Dim view As NotesView Set db = s.currentDatabase Set view = db.GetView( &quot;XML&quot; ) Set doc = view.GetFirstDocument Print &quot;Content-type: text/xml“ Print &quot;<BOOKCATALOG>“ While Not ( doc Is Nothing ) Print &quot;<BOOK>&quot; Print &quot;<bookTitle>&quot;+doc.bookTitle(0)+&quot;</bookTitle>“ Print &quot;<bookAuthor>&quot;+doc.bookAuthor(0)+&quot;</bookAuthor>&quot; Print &quot;<bookPrice>&quot;+doc.bookDiscountPrice(0)+&quot;</bookPrice>&quot; Print &quot;<bookCategory>&quot;+doc.bookCategory(0)+&quot;</bookCategory>&quot; Print &quot;</BOOK>&quot; Set doc = view.GetNextDocument( doc ) Wend Print &quot;</BOOKCATALOG>&quot;

Dim s As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Dim view As NotesView

Set db = s.currentDatabase

Set view = db.GetView( &quot;XML&quot; )

Set doc = view.GetFirstDocument

Print &quot;Content-type: text/xml“

Print &quot;<BOOKCATALOG>“

While Not ( doc Is Nothing )

Print &quot;<BOOK>&quot;

Print &quot;<bookTitle>&quot;+doc.bookTitle(0)+&quot;</bookTitle>“

Print &quot;<bookAuthor>&quot;+doc.bookAuthor(0)+&quot;</bookAuthor>&quot;

Print &quot;<bookPrice>&quot;+doc.bookDiscountPrice(0)+&quot;</bookPrice>&quot;

Print &quot;<bookCategory>&quot;+doc.bookCategory(0)+&quot;</bookCategory>&quot;

Print &quot;</BOOK>&quot;

Set doc = view.GetNextDocument( doc )

Wend

Print &quot;</BOOKCATALOG>&quot;

How Domino Data Looks as XML

Using XML with Excel Read XML from a Notes database XML generated via an agent in Notes database Chart the data using Excel charting tools Works with Domino 5.X, 6.X, 6.5.X, 7, and 8 For this example, we will not write any code!

Read XML from a Notes database

XML generated via an agent in Notes database

Chart the data using Excel charting tools

Works with Domino 5.X, 6.X, 6.5.X, 7, and 8

For this example, we will not write any code!

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

Four Main Solutions Available Crystal Reports IntelliPRINT Analytics/Reporting Swing Integra4Notes/Integra Quick Reports

Crystal Reports

IntelliPRINT Analytics/Reporting

Swing

Integra4Notes/Integra Quick Reports

Crystal Reports Top of the line reporting Designed for developers and top end power users Version bundled with Visual Studio.NET No built in Notes awareness Does not know Notes database design out of the box Uses ODBC connectivity today Used to have a Notes driver; it was removed a few years ago Can be very slow Client installs required The most expensive of the four

Top of the line reporting

Designed for developers and top end power users

Version bundled with Visual Studio.NET

No built in Notes awareness

Does not know Notes database design out of the box

Uses ODBC connectivity today

Used to have a Notes driver; it was removed a few years ago

Can be very slow

Client installs required

The most expensive of the four

IntelliPRINT Reporting Basic reporting functionality Advanced reporting capabilities are difficult to use No end-user reporting functionality Development of reports will be a struggle for most Notes Developers Client installs required No server side report generation

Basic reporting functionality

Advanced reporting capabilities are difficult to use

No end-user reporting functionality

Development of reports will be a struggle for most Notes Developers

Client installs required

No server side report generation

Swing Integrator Developers’ toolkit vs. Product Swing Wizard adds design elements to each application Forms, script libraries, views, etc. Subform and code added to each form Works on the Web and in the Notes client Functionality out of the box is very good Extending the functionality is difficult to impossible

Developers’ toolkit vs. Product

Swing Wizard adds design elements to each application

Forms, script libraries, views, etc.

Subform and code added to each form

Works on the Web and in the Notes client

Functionality out of the box is very good

Extending the functionality is difficult to impossible

Integra4Notes and Integra Quick Reports Integra4Notes A Notes application that allows importing and exporting of data between Lotus Notes and Word, Excel, PDF files, and XML files A powerful tool for the Notes developers Integra Quick Reports Ad-hoc reporting for end-users to Microsoft Excel Mail merge and labels to Microsoft Word From any Lotus Notes database

Integra4Notes

A Notes application that allows importing and exporting of data between Lotus Notes and Word, Excel, PDF files, and XML files

A powerful tool for the Notes developers

Integra Quick Reports

Ad-hoc reporting for end-users to Microsoft Excel

Mail merge and labels to Microsoft Word

From any Lotus Notes database

Integra4Notes Key Features and Benefits More advanced capabilities Event driven script, for example Access multiple databases and sources Oracle, SAP, mySQL, etc. Non-intrusive — no designer changes Zero footprint — no exe, no DLLs , etc. Distribute via email, save to Notes document, send doclinks, send to printer, fax, etc. Notes client, browser, and scheduled Export and import Generate PDFs

More advanced capabilities

Event driven script, for example

Access multiple databases and sources

Oracle, SAP, mySQL, etc.

Non-intrusive — no designer changes

Zero footprint — no exe, no DLLs , etc.

Distribute via email, save to Notes document, send doclinks, send to printer, fax, etc.

Notes client, browser, and scheduled

Export and import

Generate PDFs

Third-Party Application Demonstration

Agenda Introduction Definitions The basics and Microsoft Word Microsoft Excel Charting Microsoft PowerPoint Web Services and Microsoft Word Office and XML Third Party Solutions More More More Q & A

Introduction

Definitions

The basics and Microsoft Word

Microsoft Excel Charting

Microsoft PowerPoint

Web Services and Microsoft Word

Office and XML

Third Party Solutions

More More More

Q & A

More More More Microsoft Infopath and Web Services VB.NET and Domino COM Visual Studio Tools for Office ASP.NET Visual Studio.NET 200X (“Orcas”) etc

Microsoft Infopath and Web Services

VB.NET and Domino COM

Visual Studio Tools for Office

ASP.NET

Visual Studio.NET 200X (“Orcas”)

etc

 

Wrap-up Demonstrated multiple technology paths to reach the same goal Discussed COM/OLE, Web Services, and XML Walked thru lots of examples Hopefully gave you ideas on how to add value to your applications

Demonstrated multiple technology paths to reach the same goal

Discussed COM/OLE, Web Services, and XML

Walked thru lots of examples

Hopefully gave you ideas on how to add value to your applications

Resources MSDN http://msdn.microsoft.com InteropTips.com http://interoptips.com John Head’s blog http://www.johndavidhead.com Slides and content update available from http://www.psclistens.com

MSDN

http://msdn.microsoft.com

InteropTips.com

http://interoptips.com

John Head’s blog

http://www.johndavidhead.com

Slides and content update available from

http://www.psclistens.com

Thank you! Questions & Answers Please remember to fill out your evaluation. [email_address] www.johndavidhead.com www.psclistens.com

Thank you!

Questions & Answers

Please remember to fill out your evaluation.

[email_address]

www.johndavidhead.com www.psclistens.com

Add a comment

Related pages

Office Integration | LinkedIn

View 199 Office Integration posts, presentations, experts, and more. ... · Desktop Integration Framework Which Office versions are certified?
Read more

ILUG2007 » pmooney.net

ILUG event, Eileen has reached ... MS Office and Notes & Domino integration. Rocky Oliver: 9.1. 8.9. ... Many of the people that attended ILUG2007 will ...
Read more

John Head PSC Group, LLC - IamLUG

John Head PSC Group, LLC ... Publications on Office and SmartSuite integration with Notes ... Notes 8.5.X Office 2007
Read more

John D. Head

John D. Head CONSULTANT ... the award winning Notes to Office integration and reporting product, ... ILUG 2007 Presentation ...
Read more

DomBlog.de - Notes, Domino and Stuff

... Thomas Lang and Jens Polster blogging about Notes, Domino and Stuff. ... 14.08.2007 : Notes/Domino ... Office Integration with IBM Lotus Notes ...
Read more

Lotus Guru

... CoComment Now Disabled on Lotus Guru; 08/02/2007 : ... CardScan Integration with Lotus Notes; 07/26/2007 : ... 05/31/2007 : The ILUG Quintet ...
Read more

It Office Integration | LinkedIn

It Office Integration. Articles, experts, jobs, and more: get all the professional insights you need on LinkedIn. Sign up Get more personalized results ...
Read more

Freiberufler: Experte (PCLP) für IBM Lotus Notes Domino ...

Bewerber für Festanstellungen (ehemals Randstad Professionals) Bitte hier anmelden. Noch nicht Mitglied? Jetzt registrieren. Experten finden
Read more