=
Geoff
Schaller
=
Ed
Richard
Introduction
This will be an unusual
discussion in that whilst there will be some VO, we will be taking you to =
some
important non-VO tools. Whilst we will certainly demonstrate some importan=
t integration
aspects, our aim is to demonstrate how we can employ MS Office products fr=
om
our VO desktop application. This is about integration: about making our
applications smarter and by making MS Office products smarter, more tailor=
ed to
the job at hand in an average business environment.
So let’s start wit=
h a
question: why doesn’t anyone write a spreadsheet application in VO
anymore? The answer is simple. For less than $100 you can buy something wi=
th
about 10 years + development in it and backed by a mega machine called
Microsoft. And let’s face it, it does the job very, very well and al=
ready
integrates with a host of other related office software. Except maybe for =
email
(where control issues are involved), most office deployment suites such as=
word
processors, storage, backup and imaging software already exists far cheaper
than we could ever duplicate it for.
But as desktop and web d=
evelopers
we have something that these office tools usually don’t have: the of=
fice
database and data warehouse. What is often annoying to a client is the
laborious need to have several applications running simultaneously just so=
that
data from system can be referenced by another or deployed into a separate =
tool.
So the question becomes not what we can create for the user but how we can
integrate those tools to provide better productivity to the end user. Luck=
ily
this is an area where Microsoft is investing a lot of money and resources.=
And
for obvious reasons; it will ensure the MS-Office suite is there to stay on
user’s desktops.
This is what this paper =
is about:
integrating VO desktop applications with Microsoft’s MS Office 2003 =
and
2007 range of products. When your client wants to raise a standard letter =
about
a client, he wants and should be able to have organic access to client data
from within his Word document. Equally, he would want to seamlessly launch=
his
document editing session from the application and return to when complete.=
It
will be these kinds of features we will demonstrate to you in this paper. =
The
emphasis for this article is on presenting concepts and an overview, you w=
ill
find more detailed explanations of various techniques in separate articles=
on
the VOConversion.com.au Web Site.
Microsoft
Design Goals
From the very earliest days Microsoft has had an attitude that program=
mers
need access to the object model of its software. The idea was that if they
could convince developers to use and deploy Microsoft products from within
their own applications, they would not compete on a direct level but would=
act to
improve Microsoft sales. They were right. This is exactly what has happene=
d.
Newer tools like VSTO seriously add to a developers toolbar for integratio=
n.
Now it goes without saying that this hasn’t always been very eas=
y!
The early days of CDO (Collaborative Data Objects) was quite complex and
documentation for many of Microsoft’s product object models has been
scant and unreadable. However, each successive generation of MS Office has=
seen
substantial improvement. With MS Office 2003 and other related initiatives,
documentation has never been better and the new XML based tools start to o=
ffer
real flexibility. Finally, with release 2005 Visual Studio we can code doc=
ument
scripts in our favourite development language and this is a significant st=
ep
forward. VSTO, VSTO-SE and Office 2007 have taken the meaning of integrati=
ng
with the Microsoft Office System to a new level. In this article we will s=
tick
to MS-Office 2003, as this is currently often found on users desktops but =
all
of the proposed solutions will work equally well with Office 2007 and futu=
re
articles will explore the new development opportunities further.
The goals MS has for the near future go even further, the next version=
of
Office will be positioned as the ultimate collaboration environment and ad=
d a
lot of server based services. SharePoint and SQL server will become a very
integrated part of that picture. We already see that users spend a lot of =
time
in Outlook, and often prefer working from Outlook as compared to our
applications making use of Outlook features. Now, we can fight this, or we=
can
try to integrate and make good use of it, what do you prefer?
So we take at face value Microsoft’s desire to make everything we
need in these tools accessible to us as developers and provide us with fra=
meworks
to do the same the other way around. We shall now investigate who we can p=
ut
this into practice.
The
Tools of Integration
The
Browser
The browser is the most underrated feature of Microsoft’s vision=
for
software universality. The browser is a concept invented right back before
Windows 3.1 and was intended to be the universal container for hosted
application environments. Did you wonder at the origin of the ShellExecute=
()
concept and why it had such specific functionality? For example, what is t=
he difference
between an Internet Explorer and Windows Explorer? Nothing… except f=
or
the toolbars and a little bit of layout. Functionally they are the same th=
ing.
Let me prove it, follow along. IIS is running on this PC so that I can
demonstrate serving a web page. I’ll open the web browser (Internet
Explorer) and type in C:\Program Files – yes, I get my HDD listing. =
Now
I’ll open a normal windows explorer (see the local machine) but
I’ll type a web address. Bingo! Now I’ll type in a document na=
me
and up pops the editor.
So how do we use this from within an application and where is it usefu=
l? We
will come to the first question later with a short demo but first we need =
to
consider the “why”. The main reason is to contain functionalit=
y or
maintain control over the intended application. For example, consider a
circumstance where we want our user to choose or modify document templates=
for
standard letters. Of course we can load MS Word and run the application bu=
t by
using a browser, we contain restrain the application to within ours. Later=
we
will consider the full blown use of MS Word but for now consider a limited
need. The first step is to create a container.
On our form (this will work fine for every kind of Window Class VO has=
) we
drop an OLE control:
![3D"ole=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image001.jpg")
<=
/o:p>
And in the PostInit() we initialise the control:
&n=
bsp; SELF:oDCWebBrowser:CreateEmbedding("Shell.=
Explorer")
&n=
bsp; SELF:oDCWebBrowser:InitAutoObject()
&n=
bsp; SELF:oDCWebBrowser:Toolbar :=3D 1
We will divert briefly and investigate the module OLEWebBrowser. Here =
we
can see the methods needed to execute “something” in the brows=
er.
In the demo app we can use a web address but in your commercial applicatio=
n, we
use a document. Notice we can have a bBrowser on the left of the window an=
d it
can hold a DBF with file names to document templates. Using the Notify() m=
ethod
of the window, we display the current selection in the web browser like th=
is:
&n=
bsp; SELF:oDCWebBrowser:Navigate2(SELF:server:FIELDGET(#Letter))
And it’s as simple as that. We can play around with the Navigate=
2()
method so in the sample app we load a URL, an Excel spreadsheet and a word=
doc.
Later we will explore loading a word document in some greater detail but f=
or
now we have demonstrated how simple it is control MS Word or MS Excel right
there inside your application window.
COMSDK
and VO
VO doesn’t provide much when it comes to writing your own COM
components. The Automation server class is very basic and limited but ther=
e are
alternatives, VOCOM a function library for COM and the COMSDK a Class libr=
ary
written completely in VO. The version of the COMSDK that was released rece=
ntly
also has all of the code and samples for writing Com-server applications.<=
o:p>
Some time ago (actually about 5 years) Frans de Wit b=
ecame
interested in COM and took it upon himself to implement all that is in the
‘Inside COM’ book written by Dale Rogerson. This was no easy t=
ask;
although the basics are simple, the implementation of this took a lot of t=
ime
and hard study. The book says that the best way to learn COM is by using C=
OM, and
so Frans did. The result of this effort is the COMSDK, now being a part of=
the
VO tool set. While Frans’ goal was to learn COM, our goal was to have
everything COM offers available from VO. We did run into some serious
limitations with VO’s implementation (which by the way was always
intended to be Automation, not COM).
Although the book said “The best way to learn C=
OM: use
it!”, it was maybe taken a little too literally by Frans. I do not t=
hink
that the book meant that one should write it’s own class library but=
it
did give Frans a lot of insight in COM and it’s support API’s.=
We benefit from this because the COMSDK gi=
ves VO
users a much richer COM environment. The COMSDK can be downloaded at www.comsdk.com. Frans will do a separa=
te
session and paper on how to write com-servers using the COMSDK, but =
it also
comes with a nice generic sample the dbReader, a COM server that can be us=
ed to
access any VO RDD-data source. We will have a look at how to use this comp=
onent
in the Outlook paragraph later on.
Visual
Studio and XML Support
As we move into the advanced features of MS Office products you will f=
ind
that a lot of it is XML based. In MS Office 2003, all documents can be ren=
dered
as XML and this has some surprising advantages. In combination with XSLT f=
iles,
the content of the document can be rendered differently, depending on the =
use.
For example, we might create an invoice in MS Word. The document internall=
y can
be held as “data” in an XML file but rendered in MS Word using=
an
XSLT for Word. So, take the data and apply an Excel XSLT and the same data=
can
be rendered in a spreadsheet. Of course this kind of concept suits data-ce=
ntric
output better but there is no reason why anything can’t be rendered =
any
way you wish. Think of the output format for a letter: a Word document, an
email, a web page, a spreadsheet and so on. Off course in Office 2007 it is
even more extreme, the document are in fact XML-files and there is a compl=
ete,
well documented, open API to manipulate these documents as well as some we=
ll
defined Classes in the .Net Framework to do this.
However, none of that helps our cause here. What is important is that =
the
XML nature of what is going on behind the scenes has one important benefit=
for
us. Linking in data! If we want to add intelligence to our document, how d=
o we
get it in there? The answer is schemas (XSD documents). Now although we can
write schemas manually, it is a lot easier to have some other tool do it f=
or us
and this is where Visual Studio comes in. It has a powerful schema creator=
but
it works best from a SQL database. Let’s take a look at our example:=
Visual
Objects and XML Support
Since VO 2.7, the DBFView utility has come with XML support. Although =
it is
not as powerful as Visual Studio, there will be many circumstances where i=
t is
enough. It checks and uses the MSXML parser, so if the menu options
aren’t enabled for you VO wasn’t able to create a valid
MSXML-parser. VO 2.7 checks for version 4 which should be fine on XP-machi=
nes
or with newer versions of IE installed. Using DBFView 27 you are able to s=
ave
the content of dbf-files as XML files and/or create the XSD-schema to go w=
ith
the dbf. The source code can be found in CAVO27\VOLIB\dbfview and MSXML4.A=
EF is
a VO generated automation class for the MSXML parser. Included in the sour=
ces
for this article is a version that will work out of the box on Vista with =
VO
2.8, the only difference is the actual version of MSXML it is using.<=
/o:p>
![3D"Ed_=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image002.jpg")
<=
/o:p>
This feature and option is new to VO 2.7 and offers the following outp=
ut:
![3D"ED_=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image003.jpg")
<=
/o:p>
VBScript
and VBScript Editor
Learning a little bit of VB script might sound like sacrilege but it i=
s important
if you want to optimise your use of MS Word documents or Excel spreadsheet=
s.
The approach we took, ultimately a simple one, was to create simple compon=
ents
in VO and call them from our document.
Attached in the sample folder is an application called TMBI.aef. This =
has
all the code to create two small lookup windows which we populate from our
DBFs. We will take a short tour through the application now. The important
thing to note is that this is pure VO code and relies on the classes and
objects being available. The component created merely links all the necess=
ary
libraries. The Word document executes the component through a small amount=
of
enabling script and there is where the code comes in. For example, here is=
one
snippet:
![3D"vb_=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image005.jpg")
<=
/o:p>
You can see that the VB Script opens and creates the component and the=
n on
certain events, executes method calls on our component, all of the actual =
UI
and Business logic is written in VO, or can be existing code. This is a ve=
ry
simple and very effective way to interface VO and MS Office.
MS Excel
MS Excel is a much underrated way to enhance your application. At the
simplest level it allows you to extend data grids into Excel so that the c=
lient
may manipulate data offline. We often do this where we don’t want to
offer a report designer or something more complex in the way of reports. I=
t is
also a cheap way to export data because once in MS Excel, it is a simple s=
tep
to re-save in a new format, email or print the data. Of course the user no=
w has
the ability to resort and total to their heart’s content.
There are a few ways to use MS Excel – an easy way and more comp=
lex
ways. All have their merits but we will start with the easy way: generate =
a CSV
file and “open” it. The simplicity is staggering. At its simpl=
est,
assume we have a data grid comprising a 10 column by 20 row array. Some va=
lues
are dates, some strings and some are numbers. The simplest call is to make=
one
huge string of the array:
&n=
bsp; &nbs=
p; cCSV
:=3D “”
&n=
bsp; &nbs=
p; FOR nN :=3D 1 UPTO nRows
&n=
bsp; &nbs=
p; =
cLineString
:=3D AsString(aData[nN,1])
&n=
bsp; &nbs=
p; =
FOR nM :=3D 2 UPTO
nColumns
&n=
bsp; &nbs=
p; =
&n=
bsp; cLineString
+=3D ","+AsString(aData[nN,nM])
&n=
bsp; &nbs=
p; =
NEXT
&n=
bsp; &nbs=
p; =
cCSV
+=3D cLineString
&n=
bsp; &nbs=
p; NEXT
&n=
bsp; &nbs=
p; MemoWrit(“MyFile.cvs”,
cCSV)
And that is it. There are a couple of things to remember. Any strings =
that
potentially contain commas should be manipulated using StrTran() to remove
them. Next, when you inspect your result in Excel you may see that Excel is
unable to format the columns based on the details contained. You can often=
“cheat”
Excel by providing enforced spaces within the fields, 0’s in front of
numbers and choosing your date formats carefully. However, everything must=
be a
string. So how does Excel pick this up? Like this:
ShellExecute(NULL_PTR,
PSZ("OPEN"),String2Psz((“MyFile.cvs”),NULL_PSZ,NULL_=
PSZ,SW_SHOWNORMAL)
Very easy and very effective. Of course the data can come from a DBF, =
an
array or a reformatted text file. The above demo builds a string in memory=
but
for really large executions a logfile class could be used to manipulate the
data array. Appendix A shows a more detailed example where we implant colu=
mn
headings and do some file name preparation. Note that if the filename cont=
ains
illegal characters nothing will happen and you won’t receive any not=
ification
to the effect.
This is one quick and effective way but it obviously suffers some
inconveniences. Formatting is the most obvious: Excel has no way to identi=
fy
content, width or formatting so it simply guesses. If a more sophisticated
output is required then we need to go the next step and run an automation
server. Fortunately this is also not very difficult. Consider the following
code:
&n=
bsp; &nbs=
p; oExcelApp
:=3D _Application{"Excel.Application"}
&n=
bsp; &nbs=
p; oExcelApp:Caption
:=3D "A VO/Excel
Application"
&n=
bsp; &nbs=
p; oExcelApp:SheetsInNewWorkbook
:=3D 2 //number of Worksheet=
s
&n=
bsp; &nbs=
p; oWorkbooks
:=3D oExcelApp:Workbooks
&n=
bsp; &nbs=
p; oWorkbooks:Add()
&n=
bsp; &nbs=
p; oWorksheet
:=3D oExcelApp:ActiveSheet
&n=
bsp; &nbs=
p; oWorksheet:Name
:=3D "VO Example" <=
/span> //Name the sheet (Tab)=
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oFont
:=3D oRange:Font &n=
bsp; &nbs=
p; =
&n=
bsp; //
modify font
&n=
bsp; &nbs=
p; oFont:Bold
:=3D TRUE
&n=
bsp; &nbs=
p; oFont:Size
:=3D 12
&n=
bsp; &nbs=
p; FOR nRow :=3D 3 UPTO<=
/span> 12
&n=
bsp; &nbs=
p; =
oRange <=
/o:p>
&n=
bsp; &nbs=
p; =
oWorksheet:[Range,"A"+AllTrim(St=
r(nRow)),"A"+AllTrim(Str(nRow))]
&n=
bsp; &nbs=
p; =
oRange
&n=
bsp; &nbs=
p; =
oRange
&n=
bsp; &nbs=
p; NEXT
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oRange
&n=
bsp; &nbs=
p; oExcelApp:Visible
:=3D TRUE //make Excel Visible<=
o:p>
Now we are driving Excel via an automation server and working directly=
with
the generated classes in VO. We can do this without the generated classes =
and
save our selves a lot of prototypes but having the classes reduces the
potential for errors. In this brief example you can see how easy it is to
reference cells and sheets, establish formatting and even enter formulas. =
You
can run this sample to see it working in practice. To get further ideas you
only need to refer to the generated classes to see the methods and propert=
ies
available. Beyond that, experimentation is the order of the day.
![3D"Exc=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image006.jpg")
<=
/o:p>
We will look at other ways of manipulating data in Excel in the Articl=
es
planned for Office 2007, these are certainly solutions for cases where mem=
ory
is at a premium (in which case the above automation isn’t really
suitable) or when dealing with large amounts of data. In summary, you can =
see
how easy it is to interface with MS Excel and control it. Whether you open
Excel within your app or throw to a full automation copy is up to you but =
you
can leave your user with a properly formatted spreadsheet for their furthe=
r manipulation.
MS Word
Using
OLE
We can run MS Word exactly as we run MS Excel – in place activat=
ion
or as an object model. It is not necessary to cover the process in detail.
We’ve already seen most of this code but to launch an MS Word docume=
nt in
our web browser, it is as simple as this:
&n=
bsp; SELF:oDCWebBrowser:CreateEmbedding("Shell.=
Explorer")
&n=
bsp; SELF:oDCWebBrowser:InitAutoObject()
&n=
bsp; SELF:oDCWebBrowser:Toolbar :=3D 1
&n=
bsp; SELF:oDCWebBrowser:Navigate2(SELF:server:FIELDGET(#Letter))
The key is the Navigate2 method. An automation version is as simple as
this:
&n=
bsp; oWord
:=3D OLEAutoObject{"Word.Application"}
&n=
bsp; oWord:Document=
s:Add(cFileName)
&n=
bsp; oDoc
:=3D oWord:ActiveDocument
&n=
bsp; oDoc:PrintOut(=
)
=
; oDoc:Saved
:=3D TRUE
&n=
bsp; oWord:Quit()
Document
Templates and Schema
Another important feature of
using MS Word is managing a document design by template. The templa=
te
will carry all the event handlers, COM references, data schemas and anythi=
ng
else required to manage access to your VO code. We won’t go into det=
ails
now but you can easily add smart quotes, tool bars, buttons or any other
regular feature of GUI windows you see to make access to your features much
easier. A little bit later i=
n the
paper we will cover using schemas to carry links to data fields that our C=
OM
components will populate. All these tools ultimately link together. Here i=
s an
example of the document showing its schema fields:
![3D"Tem=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image008.jpg")
<=
/o:p>
The schema attached to this was created in Visual Studio but of course=
it
can be created in any convenient manner. MS Word either displays or hides =
the
fields (Shift-Ctrl-X) in the working template but of course the user gener=
ated
document has these fields completed as text. Here is what some of the sche=
ma
(XSD) looks like:
<?xml
version=3D"1.0" encoding=3D"utf-8" ?>
<xs:schema
id=3D"StandardLetterSchema"
targetNamespace=3D"http://tempuri.org/StandardLetterSchema.xsd"<=
o:p>
=
elementFormDefault=3D"qualified"
xmlns=3D"http://tempuri.org/StandardLetterSchema.xsd"
xmlns:mstns=3D"http://tempuri.org/StandardLetterSchema.xsd"
&n=
bsp; xmlns:xs=3D"http://www.w3.org/2001/XMLS=
chema"
xmlns:msdata=3D"urn:schemas-microsoft-com:xml-msdata">
&n=
bsp; <xs:element
name=3D"StandardLetterFields">
&n=
bsp; <xs:complexType>
&n=
bsp; &nbs=
p; <xs:sequence>
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.CFILE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.NAME" msdata:Caption=3D"Testing here"
type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.TITLE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.GIVENNAMES" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.SURNAME" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.ADDRESSLINE1" type=3D"xs:string"
msdata:Caption=3D"ADDRESS LINE1" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.ADDRESSLINE2" type=3D"xs:string"
msdata:Caption=3D"ADDRESS LINE2" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.ADDRESSLINE3" type=3D"xs:string"
msdata:Caption=3D"ADDRESS LINE3" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.POSTCODE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.BIRTHDATE" type=3D"xs:string"
minOccurs=3D"0" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.PHONE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.MOBILE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.FAX" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.EMAIL" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.SEX" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"CLIENT.GAB" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.CODE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.NAME" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.CONTACT" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.TITLE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; <xs:element
name=3D"AGENT.GIVEN" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.SURNAME" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.ADDRESSLINE1" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.ADDRESSLINE2" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.ADDRESSLINE3" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.PHONE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.MOBILE" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.FAX" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.EMAIL" type=3D"xs:string" />
&n=
bsp; &nbs=
p; =
<xs:element
name=3D"AGENT.NOTES" type=3D"xs:string" />
&n=
bsp; &nbs=
p; </xs:sequence>
&n=
bsp; </xs:complexType>
&=
lt;/xs:element>
</xs:schema>
In the Article “Generati=
ng
Business Documents using XML..” on the VOConversion WebSite we explain this process in detail using s=
tep
by step examples.
Smart Documents
Definitely your VO applications are great tools for the users you̵=
7;ve created
them for, and integrating Office applications will make them even better by
allowing users to combine the tools they’ve become used to with the
customized data, context and the content your application delivers. But th=
ere
are probably many users in your organization and at your customers’ =
sites
that are not using your applications: it may not be their primary job, the=
y may
not have been trained or maybe its beyond their security level. It might a=
lso
not be worth it, the applications are too big and complex, and they would =
only
use a very small part of it. In other words your application would be over=
kill
to them. Another scenario might be that the application needs to be instal=
led
and needs pre-requisites that aren’t available to those users, like =
network
connectivity or a powerful workstation. In those cases deployment might pr=
event
them from using the functionality your application provides.
In those cases the Office suite provides a platform to deliver special=
ized
pieces of your applications to all Office users in the company or at your
customers’ site. In this context it’s worth taking a look at t=
hree
different concepts that could come in handy;
- Plain Web Service Integration.=
<=
span
style=3D'mso-tab-count:1'> Micro=
soft
did a good job making sure Web Services can be used in many of the Of=
fice
applications, they made a toolkit available that supports good
integration. We will have a look at this from Excel. For the example
we’ll show a Web Service that is running on our server in
The toolkit can be downloaded from MSDN .
- The Research Pane.<=
span
style=3D'mso-tab-count:1'> &=
nbsp; Office
2003 introduces the Task Pane, an interesting way to build a small pa=
rt of
a UI that fully integrates in the Office applications. You pull up the
task pane using Ctrl F1 and it nests itself on the right of your scre=
en.
The actual panel we can interact with using Web Services is the Resea=
rch
pane, you can pick that using the little black down arrow and clickin=
g on
‘Research’. It already comes with a range of Services, bu=
t we
are most interested in how to make our own available in there. For th=
is we
need to write a Web Service that implements a specialized programming=
interface
which is also described on MSDN. There is also a Visual Studio soluti=
on
available in the form of a toolkit that delivers an empty, working
Research Service, so all we have to do is import that in start fillin=
g in
our business logic (which we already have written in VO). So it’=
;s
all just about one more layer of plumbing. The great thing about this
approach is that once you have your infrastructure set up like this, =
it
will automatically be available to all users and in all Office
applications that support the Task Pane.
- Smart Documents.=
&=
nbsp; This
is the most complex step of the three described, but also offers the =
most
power to the developer. The drawback is that it usually requires a bi=
t of
setup or deployment because in this case the documents themselves are
hosting the solution code, either just in the form of XML-schema̵=
7;s,
but often also combined with DLL’s. What it actually comes down=
to
is that a developer has the ability to attach his code or events to a
certain context in the document. This context is provided by the appl=
ied
XML-schema. Typically the UI is exposed in the Task Panel.
- Customised Task Pane<=
span
style=3D'mso-spacerun:yes'> Using VSTO, the Visual Studi=
o Tools
for Office it is fairly easy to write your own Task Panes we will
investigate this further in a future article on VoConversion.
It’s clear that to make good use of these tools and the integrat=
ion
they provide you would have to start creating Web Services. This is not a
difficult task, using Visual Studio to create the plumbing and the COMSDK =
to
create your automation servers that access your existing Business Logic.
For more detailed explanations of these Web-savvy features and some gr=
eat
samples, see Scott Hillier’s book: Microsoft SharePoint: Building Of=
fice
2003 Solutions. Almost all of these discussion topics can be found there a=
nd I
would draw your specific attention to Chapter 8 where schemas are mentione=
d.
And remember Web Services are not just for Internet applications, they work
fine in a local network.
The Next
Step: Information Business Framework: IBF
Just to give you a glimpse of what is in store for the future, Microso=
ft is
clearly investing in these technologies and building upon them. And rightf=
ully
so, when an organisation starts implementing this, (Web) Services will blo=
ssom
but also present a risk: management wise, security wise and who will know =
what
is there and when to use it? This is why MS invented a Framework that allo=
ws us
to classify and publish our services and to determine context and roles so=
the
right users get the appropriate content and functionality delivered to the=
ir
desktop applications. A very interesting development. Here’s
where you read all about it, and this
links to a technical discussion about version 1.0, but 1.5 is around t=
he
corner.
![3D"http://msdn.microsoft.com/library/en-us/odc_ibf2003_ta/html/idc_ibf=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image010.jpg")
![3D"Double](3D"file:///C:/8C69C633/VOwithOffice2003_files/image011.gif")
Why the parenthesis around “web” y=
ou
might ask?, well when Indigo is released (which it will be pretty soon) the
infrastructure will be independent of hardware boundaries and we will be a=
ble
to use the same model between applications on a desktop, on a LAN or even
within a single application. The programming model for this will be simple=
to
implement but extremely powerful and scalable building upon experiences wi=
th
Web Services and WSE as well as COM+ and MSMQ. It will support transaction=
s,
security and message queuing and can guarantee delivery and reliability wh=
en
needed.
.
MS
Outlook
We shouldn’t have to ask it but the general question might be: w=
hy
use Outlook? The answer is obvious: because most of the business world use=
s it.
Once you are in an office with more than 5-10 users in a network, you will
generally find Outlook. Most office employees are familiar with it and des=
pite
its numerous quirks, it works. It is an extremely comprehensive package,
especially when backed by MS Exchange and most small businesses use it. If=
the
client is using Windows Server 2000 and later or is using Small Business S=
erver
then Outlook will probably be the basis of their workflow. We will now exp=
lore
the three most commonly used items: Email, Tasks and Calendar entries.
Tasks
Generating tasks is a very powerful mechanism over emails in that they
carry their own workflow aspects. Most people are familiar with tasks in
Outlook so we don’t need to convince you of their worth but like
calendars and email, it is a feature of Outlook in wide use. We have writt=
en a
small collaboration component to handle the task creation from VO code like
this:
METHOD
CreateTask(cSubject, cNotes, cCFile, cCompany, cWhoFor, dExpiry, cPriority,
cCategory) CLASS Diary
&n=
bsp; &nbs=
p; LOCAL cbError AS CODEBLOCK
&n=
bsp; &nbs=
p; LOCAL o AS
TS_ITMCollabApplication
&n=
bsp; &nbs=
p; cbError
:=3D ErrorBlock({|err| _Break(err)})
&n=
bsp; &nbs=
p; BEGIN SEQUENCE=
&n=
bsp; &nbs=
p; =
o
:=3D TS_ITMCollabApplication{}
&n=
bsp; &nbs=
p; =
cClientID
:=3D Trim(cCFile) + ";" + Trim(cCompany) + ";" +
DToS(dExpiry) + ";" + cPriority + ";" + cCategory
&n=
bsp; &nbs=
p; =
o:CreateTaskRequestClient(cTargetRecipient=
,cSubject,cBody,cClientID)
&n=
bsp; &nbs=
p; =
o:Destroy()
&n=
bsp; &nbs=
p; =
ErrorBlock(cbError)
&n=
bsp; &nbs=
p; RECOVER
&n=
bsp; &nbs=
p; =
ErrorBlock(cbError)
&n=
bsp; &nbs=
p; =
LogComment("OLE_Error_"+NTrim(Se=
conds())+".log",
cClientID)
&n=
bsp; &nbs=
p; =
cBody
+=3D CRLF2 + "No Task Generated"
&n=
bsp; &nbs=
p; =
SELF:Skip(0)
&n=
bsp; &nbs=
p; =
SELF:FIELDPUT(#NOTES, cBody)
&n=
bsp; &nbs=
p; =
SELF:Commit()
&n=
bsp; &nbs=
p; END SEQUENCE
=
;
&n=
bsp; &nbs=
p; RETURN
We will look at the Collaboration component in detail= but it simply contains the VO equivalent of the VB-script shown below.
SUB TestdbReader()
SET IXPServer =3D CreateObject("TS_DBReader.Server")
SET IXPRecordsetFactory =3D IXPServer.IRecordsetFactory(-1,
"DBFCDX", "h:\vo27rel\treq.DBF")
lShared =3D TRUE
lReadOnly =3D FALSE
SET IXPRecordset =3D IXPRecordsetFactory.IRecordsetEx(lShared, lRea=
dOnly)
SET IXPCursor =3D IXPRecordset.ICursor
Tmp =3D IXPCursor.gotop()
WHILE Not IXPCursor.IsEOF
=
; Done
=3D IXPCursor.FieldValue(1)
=
; IF
Done =3D "N" Then
=
; &=
nbsp; &nb=
sp; MsgBox
IXPCursor.FieldValue(2)
=
; &=
nbsp; &nb=
sp; Set
NewItem =3D Application.CreateItem(olTaskItem)
=
; &=
nbsp; &nb=
sp; NewItem.ASSIGN
=
; &=
nbsp; &nb=
sp; NewItem.Recipients.Add
(IXPCursor.FieldValue(6))
=
; &=
nbsp; &nb=
sp; NewItem.Recipients.ResolveAll
=
; &=
nbsp; &nb=
sp; NewItem.Subject
=3D IXPCursor.FieldValue(7)
=
; &=
nbsp; &nb=
sp; NewItem.Body
=3D IXPCursor.FieldValue(8)
=
; &=
nbsp; &nb=
sp; NewItem.Save
=
; &=
nbsp; &nb=
sp; NewItem.Send
//Now need TO write “Y” IN Done FIELD.
=
; END
IF
=
; Tmp
=3D IXPCursor.Skip(1)
=
; Wend
END Sub =
;
This simple chunk of code opens a DBF we created and = reads in the values into task items for execution. The VO code to fill this DBF = is as follows:
cClientID :=3D Trim(c=
CFile) +
";" + Trim(cCompany) + ";" + DToS(dExpiry) + ";&q=
uot;
+ cPriority + ";" + cCategory
o :=3D TaskRequests{,=
"TREQ"}
o:Append()
o:FIELDPUT(#body,cBod=
y)
o:FIELDPUT(#subject,c=
Subject)
o:FIELDPUT(#reqstring=
,cClientID)
o:FIELDPUT(#TOEMAIL,c=
TargetRecipient)
o:FIELDPUT(#RDTS,DToS=
(Today())+Time())
o:FIELDPUT(#Done,&quo=
t;N")
o:Commit()
o:Close()
In summary,=
the
process is rather straightforward – the mechanism is the only choice.
Depending on where the task is being generated from and who is executing t=
he
task, you may use either of these methods. We can generate them directly or
indirectly as shown.
Email
There is one compelling reason why we would choose Outlook over Outlook
Express for automated email. Outlook Express does not have an object model=
or
automation server so it is a standalone application. Thus we are unable to
interface effectively with it. Using our VO2Outlook library, here is all t=
he
code we need in order to generate an email:
METHOD EmailButton( ) CLAS=
S
EditAgencyWindow
&n=
bsp; LOCAL oMailItem=
&n=
bsp;
&n=
bsp; LOCAL oOutlook&=
nbsp; &nb=
sp; AS TS_UIOutlook
&n=
bsp; LOCAL lRetVal&n=
bsp; &nbs=
p; =
AS LOGIC
&n=
bsp; LOCAL cBody&nbs=
p; =
&n=
bsp; AS STRING
&n=
bsp; LOCAL cSubject&=
nbsp; &nb=
sp; =
; AS STRING
&n=
bsp; LOCAL cTargetRecipient &=
nbsp; AS STRING
&n=
bsp;
&n=
bsp; cBody
:=3D ""
&n=
bsp; cSubject
:=3D ""
&n=
bsp; cTargetRecipient
:=3D Trim(SELF:oDCemail:TextValue)
&n=
bsp; lRetVal
:=3D TRUE
&n=
bsp; oOutLook:=3D
TS_UIOutlook{}
&n=
bsp; IF oOutlook <> =
NULL_OBJECT
&n=
bsp; &nbs=
p; oMailItem:=3DoOutlook:Application:CreateMailItem()
&n=
bsp; &nbs=
p; IF oMailItem<>N=
ULL_OBJECT
&n=
bsp; &nbs=
p; =
oMailItem:CABody =
:=3D
cBody
&n=
bsp; &nbs=
p; =
oMailItem:CASubject =
:=3D
cSubject
&n=
bsp; &nbs=
p; =
oMailItem:CARecipients:CMAdd(cTargetRecipient)
&n=
bsp; &nbs=
p; =
oMailItem:CMSave()
&n=
bsp; &nbs=
p; =
//
TO send oMailItem:CMSend()
&n=
bsp; &nbs=
p; =
oMailItem:CMDisplay()
// To display the Outook email dialog
&n=
bsp; &nbs=
p; ELSE
&n=
bsp; &nbs=
p; =
lRetVal
:=3D FALSE
&n=
bsp; &nbs=
p; END
&n=
bsp; &nbs=
p; oOutlook:Axit()
&n=
bsp; ELSE
&n=
bsp; &nbs=
p; lRetVal
:=3D FALSE
&n=
bsp; END
&n=
bsp; oOutlook
:=3D NULL_OBJECT
&n=
bsp;
&n=
bsp; RETURN lRetVal
Following through this code, we instantiate Outlook and create a mail =
item.
If we take a look at class TS_OutlookApplication we can see the primary it=
ems
we can create. As we are interested in a mail item, how do we know what we=
can
do with it, what are properties we can set and how do we ‘execute=
217;
our mail item? Go to the module VO2Outlook Main Classes and move the slider
over until you can see the mail items. Here is a preview:
![3D"out=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image012.jpg")
And perusing the remainder of the module you can easily see the range =
of other
features of Outlook and how easy it is to find out how to employ them. One=
word
about the COMSDK: you could generate the classes for Outlook yourself but =
this
version is linked in with substantial array of error handling tools in the
COMSDK. To compile this code you will require a UDC and the code base is
substantial in size. However, the benefit is much more stable code and a l=
ot
more error output information for debugging purposes.
Calendar
Creating calendar items is as simple as creating mail items. The conce=
pts
are the same: instantiate the Outlook object, create an empty item, add so=
me
properties and ![3D"Text](3D"file:///C:/8C69C633/VOwithOffice2003_files/image013.gif")
call save. Here is another code snippet which =
shows
how easy this is:
Accessing
an addresslist
Using VO2Outlook it is not very difficult to read items from the Outlo=
ok
addresslist and obviously the Outlook object model also gives us full acce=
ss to
all of the Item properties so we can change, add, delete allowing us to sy=
nchronize
it with a proprietary database when we have a need to do so.
METHOD
GetNamesFromAddressList(cAddressList) CLASS MyOutlook
LOCAL oAppl=
ication AS
TS_OutlookApplication
LOCAL oName=
Space =
AS
TS_OutlookNameSpace
LOCAL
oAddressList
LOCAL
oAddressEntries AS TS_OutlookAddressEn=
tries
LOCAL aRetu=
rn :=3D {} AS
ARRAY
LOCAL nI, n=
Entries AS
DWORD
oApp=
lication:=3DSELF:Application
IF
oApplication<>NULL_OBJECT
oNameSpace :=3D
oApplication:CMGetNameSpace("MAPI")
oAddressList
oAddressEntries<=
span
style=3D'mso-tab-count:1'> :=3D oAddressList:CAAddressEntries=
nEntries :=3D
oAddressEntries:CACount
FOR nI :=3D 1 TO=
nEntries
AAdd(aRet=
urn,
{oAddressEntries:CMItem(nI):CAName,{cAddressList,nI}} )
NEXT
ELSE
=
errorbox{,"oApplication<>=
NULL_OBJECT"}:Show()
=
END<= o:p>
RETURN aRet=
urn
Scripting
in Outlook
Outlook is a full blown MS Office citizen; apart from the object model=
it
also supports VBA and VB-script. Because so many users use Outlook all the
time, it might be a good idea to hook your code into Outlook instead of tr=
ying
to control Outlook from the outside. Here’s a script that uses the
dbReader component that comes as a sample with the COMSDK to access a dbf,=
process
some records and create Outlook task-requests.
Sub TestdbR=
eader()
Set IXPServ=
er =3D
CreateObject("TS_DBReader.Server")
Set
IXPRecordsetFactory =3D ;
IXPServer.I=
RecordsetFactory(-1,
"DBFCDX", "h:\vo27rel\treq.DBF")
=
;
lShared =3D True
=
;
lReadOnly =3D False
=
;
Set IXPRecordset =3D ;
=
;
IXPRecordsetFactory.IRecordsetEx(lShared, lReadOnly)
=
;
Set IXPCursor =3D IXPRecordset.ICursor
=
;
Tmp =3D IXPCursor.gotop()
=
;
While Not IXPCursor.IsEOF
=
;
Done =3D IXPCursor.FieldValue(1)
=
;
If Done =3D "N" Then
=
; &=
nbsp;
Set NewItem =3D Application.CreateItem(olTaskItem)
=
; &=
nbsp;
NewItem.Assign
=
; &=
nbsp;
NewItem.Recipients.Add (IXPCursor.FieldValue(6))
= ; NewItem.Recipients.ResolveAll<= o:p>
=
; &=
nbsp;
NewItem.Subject =3D IXPCursor.FieldValue(7)
=
; &=
nbsp;
NewItem.Body =3D IXPCursor.FieldValue(8)
=
; &=
nbsp;
NewItem.Save
=
; &=
nbsp;
NewItem.Send
End If
=
;
=
;
=
;
Tmp =3D IXPCursor.Skip(1)
=
;
Wend
End Sub
From Outlook just press Alt-F11 to start its editor. You could even go=
as
far as hooking up a function like this using a timer, pretty much the same=
as
you would in a VO-app and execute the function every few minutes.
MS
InfoPath
One of the newer additions to the Office suite is InfoPath. Basically =
it is
just a forms editor with which you can design data-input forms, distribute=
them
and save and process them afterwards. Just like the other MS Office produc=
ts,
it also offers an object model and it is a scripting host so VB-script is =
right
at home in InfoPath. But there’s more! InfoPath stores its forms as =
XML
and the design-mode actually creates views (XSLT) and schemas. That means =
the
data in a saved form is validated and conforms to the schema and can be ea=
sily
processed by applications that know how to handle XML.
Assuming you’ve already designed and published a form (publishin=
g is
done from within Infopath typically to a network share of Sharepoint Libra=
ry)
you can create new data-input forms from VO using the object model. The
following code snippet assume we have already generated the InfopathApp cl=
ass
using VO’s Automation Server tool as well as the existence of all of=
the
XML-classes MS-XML provides (same tool).
LOCAL o AS
InfopathApp
=
; LOCAL
oXDocumentCollection AS XDocuments
=
; LOCAL
oXDocument &=
nbsp; AS
OBJECT //_XDocument
=
; LOCAL
oXMLDocument =
; AS
IXMLDOMDocument
=
; LOCAL
oNames =
; AS
IXMLDOMNodeList
=
; LOCAL
oName =
AS
IXMLDOMNode
=
; o
:=3D InfopathApp{}
=
; //
Check look at version property, to see if it worked
=
; SELF:oCCOKButton:Caption
:=3D o:Version
=
;
=
; //
Tell infopath to open an InfoPath document,
// which we=
will use
as a template
=
; oXDocumentCollection
:=3D o:XDocuments
=
; //
Adjust path before trying!!
=
; oXDocument :=3D
oXDocumentCollection:Open("file:///H:/UrenDemo.xml",NIL)
// Get a po=
inter to
the "Medewerker" field (Employee),
// going th=
rough the
DOM-object and using XPath as
// the XML-=
query
language
=
; oXMLDocument
:=3D oXDocument:DOM
=
; oXMLDocument:setProperty("SelectionLanguage",
"XPath")
=
; oXMLDocument:setProperty("SelectionNamespaces",;
=
; "xmlns:my=3D'http:=
//schemas.microsoft.com/office/"+;
=
; "infopath/2003/myX=
SD/2004-07-08T07:42:01'")
=
; //
Get All nodes
=
; //
oNames :=3D oXMLDocument:selectNodes("//my:*")
=
; //
Or just
=
; oName
:=3D oXMLDocument:selectSingleNode("//my:Medewerker[1]")
=
; //
We can change it by assigning the Text property (VO generated text_)
=
; oName:text_
:=3D "Ed"
=
; //
Show it worked
=
; SELF:caption
:=3D oName:text_
// Save the=
doc and exit
the application
// Adjust path before tr=
ying!!
oXDocument:=
SaveAs("file:///h:/test.xml")
// Close it=
, but
Show or Print is also an option
oXDocumentC=
ollection:Close(0)
// And clea=
n up
oXDocument =
=
:=3D
NULL_OBJECT
oXDocumentC=
ollection
:=3D
NULL_OBJECT
o:Quit(NIL)=
o :=3D NULL=
_OBJECT
So what we are doing here is creating an Infopath form based on a temp= late, which we can than use to present to the user or send off via email for fur= ther processing. Interesting aspects of Infopath is that forms can have workflow built-in and users can digitally sign a form (or parts) which pro= vides functionality not easily created in traditional Windows applications.
MS
SharePoint
What is Windows SharePoint Services? Basically it is an office workflo=
w and
collaboration solution and it is a product that comes free with Windows Se=
rver
2003. Below is one such site=
we
created for the development of this document. At its simplest level it all=
ows
for the mutual collaboration on a document such as this: we have discussio=
ns,
create and manage tasks, store related items, manage the workflow and crea=
te
and manage versions. Because the site is interactive (and HTTP based), it =
can
be accessed from anywhere. Ed and I lived in two different time zones while
working on this article and this tool allowed us to work targeted discussi=
ons
around the document’s life cycle. The interesting part comes when you
realise that you can set events, triggers and actions based on properties =
and
methods of the site.
![3D"Sha=](3D"file:///C:/8C69C633/VOwithOffice2003_files/image015.jpg")
SharePoint has one other important characteristic: it represents a pow=
erful
storage solution. All office documentation and data can be placed in side =
it so
suddenly you are faced with an interesting possibility. Because, data,
documents and office objects are contained within, searches in SharePoint
database can now yield results that amalgamate data and documents. Put
SharePoint inside MS SQL Server and powerful search functionality becomes
available. Data is just an SQL query away… this combined with Desktop
search across all kinds of resources really puts information at the
users’ fingertips.
How does this relate to VO? In a peripheral sense, SharePoint can be s=
een a
storage location rather than the traditional hard drive. Throw in the fact=
that
you can create your own SQL based queries, SharePoint thus offers unparall=
eled
combined access to documents and data within the same results sets. If your
applications create and manage standard letters or text output, SharePoint
offers you advanced workflow management solutions that you can code into y=
our
desktop applications.
Summary
This paper has demonstrated a very small range of fea= tures that VO is capable of performing with MS Office 2003. It is not a pure VO environment and in fact we make liberal use of tools from all over. Howeve= r, you must never let this put you off. VO is a very powerful desktop applica= tions developer but no-one wants to invent too many new wheels when they already exist quite cheaply. The clever thing to do is to integrate your desktop applications into these other tools and make both sides of the equation smarter.
And smarter applications will earn you more money and= credibility in the long run.
Appendixes:
A. &= nbsp; Sending Data to Excel via CSV
B. &= nbsp; Interesting MS Office Downloads on MSDN
Bibliography
COMSDK V1.30,
2005, Frans de Wit and
Inside COM (Programming Series), Dale
Rogerson, Microsoft Press ISBN 1-15723-1349-8,
1997
Microsoft SharePoint, Building Office 2003 Solutions,&nb=
sp;
Scott
Biography:
Geoff
graduated as a computer engineer from the UNSW in 1981. He worked as an Ar=
my
Officer and Computer Engineer for the Dept of Defence for 10 years, managi=
ng
several of their mainframe and distributed computing solutions. This provi=
ded a
solid background in data management and communications. He left the Army in
1991 to pursue his own business interests and operated a food manufacturing
business employing 20 people for several years. The dissatisfaction with
software available for business management, inventory and financial manage=
ment
caused him to become involved in writing his own. Eventually, the software
interests grew to take over the manufacturing interests and so in 1999, he
joined with
Sending
Data to Excel via CSV
METHOD ExcelButton( CLASS<=
/span> ReportConfigurationWindow
=
;
=
; // oWindow wil either be the printer object or a
screen window for preview =
;
=
; LOCAL nN<=
/span>, nNN=
, nLi=
nes, nCo=
lumns
ASDWORD
=
; LOCAL oFi=
le AS
GCSLogFile
=
; LOCAL oGl=
obal AS
GCSGlobalObject
=
; LOCAL
cFileName,,ASSTRING
=
; oGlobal
:=3D GetGlobal()
=
; cFileName
:=3D StrTran((SELF:oDCM=
ainTitle:Valu=
e), "/", "-")+".CSV" =
; =
// remove slashes
=
; cFileName
:=3D StrTran(, "(", " ") =
; &=
nbsp; // remove brackets
=
; cFileName
:=3D StrTran(, ")", " ") =
; &=
nbsp; // remove brackets
=
; cFileName
:=3D StrTran(, ":", " ") =
; &=
nbsp; // remove colons
=
; cFileName
:=3D oGlobal:+cFil=
ename =
// now prefix path after the translations<=
span
lang=3DEN-US style=3D'font-size:8.0pt;font-family:"Arial","sans-serif";col=
or:black;
mso-ansi-language:EN-US'>
=
; IF Fil=
e(cFil=
eName)
=
; &=
nbsp; SHDelete(cFil=
eName, FALSE, TRUE) =
// 21/10/04 AJ: First delete file if exists
=
; ENDIF
=
; oFile
:=3D GCSLogFile{cFileName=
}
=
; IF !oFil=
e:IsOp=
en
=
; &=
nbsp; GCSErrorBox("Error opening file: "+DosErrStringoFile:LastErrorCRLF+cFileName
=
; &=
nbsp; RETURN NIL
=
; ENDIF
=
; // Print the titles into the file
=
; cLineString
:=3D cFilename
=
; oFile:Writ=
e(cLin=
eString)
=
; oFile:Writ=
e()
=
; nColumns
:=3D ALen(=
SELF:oDat=
a:aTit=
les) =
// some reports do not have titles - this fixes t=
his
=
; cLineString
:=3D ""
=
; FOR nN =
:=3D 1 UPTO nCo=
lumns
=
; &=
nbsp; IF SELF:oData:aFieldsOKnN]
=
; &=
nbsp; &nb=
sp; cLineString
+=3D ","+SELF::aTit=
les[nN]
=
; &=
nbsp; ENDIF
=
; NEXT
=
; oFile:Writ=
e(cLin=
eString)
=
; oFile:Writ=
e()
=
; // Print the data
=
; nLines
:=3D SELF:oData:Lines
=
; FOR nN =
:=3D 1 UPTO nLi=
nes
=
; &=
nbsp; // Simple array case: just print out the current =
array
element
=
; &=
nbsp; cLineString
:=3D ""
=
; &=
nbsp; FOR nNN=
:=3D 1 UPTO ALe=
n(SELF:oDat=
a:aRow=
s[nN][2]) =
// this must be the THIS row length, not no. titl=
es
=
; &=
nbsp; &nb=
sp; IF SELF:oData:aFieldsOKnNN]
=
; &=
nbsp; &nb=
sp; cElement
:=3D SELF:oData:aRows[nN][2,nNN]
=
; &=
nbsp; &nb=
sp; IF Ins=
tr(Left=
(cEle=
ment,1), "@#!?|{[{]}^&~_%"+_chr(169=
)+_chr(171=
))
=
; &=
nbsp; &nb=
sp; =
; cElement
:=3D SubStr(,2) =
&n=
bsp; // strip out control characters
=
; &=
nbsp; &nb=
sp; ENDIF
=
; &=
nbsp; &nb=
sp; DebugOutput("Adding ", ","+StrTran(, ",", ""))
=
; &=
nbsp; &nb=
sp; cLineString
+=3D ","+StrTran(, ",", "") =
; // remove commas - its CSV
=
; &=
nbsp; &nb=
sp; ENDIF
=
; &=
nbsp; NEXT
=
; &=
nbsp; oFile:Writ=
e(cLin=
eString)
=
; NEXT
=
; oFile:Clos=
e()
=
; ShellExecute(NULL=
_PTR, PSZ(&qu=
ot;OPEN"),String2PszcFileNameNULL_PSZ,NULL_PSZ,SW_SHOWNORMAL
=
; RETURN NIL
Interesting
MS Office downloads on MSDN
Microsoft
Office 2003 Web Services Toolkit 2.01
The Microsoft® Office 2003 Web Services Toolkit a=
llows
you to quickly access and use Web services with Visual Basic for Applicati=
ons
(VBA) from Microsoft Office 2003 applications.
Infopath
=
Infopath
developer resources:
http://msdn.microsoft.com/o=
ffice/understanding/infopath/training/default.aspx
=
=
=
XML in
Office and Infopath:
=
=
The
Infopath SDK:
=
Sharepoint
=
Sharepoint
Web Services
=
and:
Scott
http://www.sharepointstuff=
.com/
MS Excel
and XML
http://msdn.microsoft.com/office/default.=
aspx?pull=3D/library/en-us/odc_xl2003_ta/html/OfficeExcelXMLMappingScenari=
os.asp