Wiki Home

Automation Examples

Namespace: VB
Sample code for automation (ex OLE Automation) calls from Visual FoxPro
I created a function that checks consistency between a word document and an attached data source. I have only used it with an excel data source, though I would think the code would be fairly transportable, the caveat being this is office automation
Here is the page ... includes code Data Source Consistency Check - ?cs

When working with Automation code be sure that you completely close the application process when you have an error. Use Ctrl-Alt-Del to get the Task Manager and kill all processes for that application between your runs. These processes hold files open and eat up processor resources which can cause errors in subsequent runs.
Question: Does anybody *really* know yet to what extent Windows XP Service Pack 2 will "break" existing apps that use a lot of OLE automation? We do a ton of Office automation in our custom VFP apps, including high volume Word merging, building Excel sheets, Outlook automation, etc. See the following (vague) article:

This has 3 possibly major implications: 1. How do we get clients to agree to pay for regression testing Windows and "fixing" their apps (if needed), especially if they can just claim we did not follow "best practices"?, 2. How do we justify "eating" the cost of potential "fixing" especially if our schedules are full of existing project work?, 3. What if whatever "breaks" can't be fixed? (realizing automation opens up a lot of possibilities even Microsoft may not have thought of) Can we/should we just tell clients to NEVER update Windows XP past a certain point? -- Randy Jean
Can I automate Ms SQL EM? What I really want is GenDbc for SQL, kind of like EM's Generate Script but witout the wizard interface - I want to hard code the options in a prg. - ?cfk

I don't know of a way to automate the interface, but you certainly can automate all the things that the EM can do using the Sql DMO (SQL Distributed Management Objects), which, IIRC, is what the EM itself does. -- ?acc

Cfk, you say you want the equivalent of GedDBC for SQL. Did you know that in SQL Server, almost any taks can be scripted? Basically, you perform the task in Enterprise Manager, and tell it to create a script at the same time. So, if you interactively create a table in EM, the script will contain the required CREATE TABLE statements. That way, you can produce code to generate an entire database schema. Mike Lewis
Local loWord
loWord = CREATEOBJECT("Word.Application")
loWord.ActiveDocument.Selection.Type("I love FoxPro!")

I cut and pasted this and got the following error:
OLE error code 0x80020011: Does not support a collection.
on the 'type' line. Is this code specific to O2K?

It's better not to work with Word's Selection object, but if you do, you need to remember that it's a member of the Word Application object, not of Document. There's only one Selection object for the entire Word instance. A better way to do this is:
Local loWord
loWord = CREATEOBJECT("Word.Application")
loRange = loWord.ActiveDocument.Range()
loRange.InsertAfter("I love FoxPro!")
release loRange, loWord

Also, be aware that if you close Word too soon after printing, it can raise an error.
Use the Background Printing Status property to see if Word is ready to be closed.
To generate and send email from MS Outlook:

More generally, see Outlook Automation.

( Note: This doesn't work with OutlookExpress or other email programs, such as Eudora. Check out Tapi Fax Automation for a way to control whatever TAPI-compliant email program is registered. Eudora Is TAPI-Compliant... I don't know about OE -- ?wgcs)

oOutLookObject = CreateObject("Outlook.Application")
oEmailItem = oOutLookObject.CreateItem(MAILITEM)

WITH oEmailItem
   .Recipients.Add("[email protected]") && uses the Recipients collection
   .Subject = "Automation sample"
   .Body = "This is easy!"
   .Attachments.Add("c:\mydir\sample.txt") && Note that the fully qualified path and file is required.

RELEASE oEmailItem
RELEASE oOutLookObject

Note that attachments is a collection inside the message object.

For more details, check the excellent series of articles by John Petersen in Fox Talk August and September 1998

-- Alex Feldstein

Check the article
Also check out OLE section of Knowledge Base Watch for other samples.
How can I spell check a Memo field using Word and save corrections?

In this example, mytable.memofield is the field.....

REPLACE mytable.memofield WITH cText

--- John Koziol
How can I parse an address using Outlook?
See Parsing Addresses

Word automation page breaks
I am using Word automation to generate a document using info pulled in from a table. Some of the sections are several pages long, so I'm using the following logic to control the page breaks:

  oRange.Collapse(0)  && wdCollapseEnd
  nVPos =
  cText = < formatted text based on the current record >
  nInches = Thisform.nLineHt * INT(LEN(cText)/Thisform.nCharsPerLine)+ 1
  IF nVPos + nInches > Thisform.nBottomMargin && Not enough room on page
     oRange.InsertBreak(7) && wdPageBreak
     oRange.InsertAfter([(Section 1, cont.) ]+CR+CR)

The various form properties nPtsPerInch, nLineHt, etc are set prior to running the routine based on the font selected, etc. In theory, nVPos is the vertical position in inches from the top of the page.

This works fine on my system here using Office XP, but when I get weird page breaks when I run it on the client’s machine (which has Office 2000). After inserting some debugging code, I determined the problem to be in using the value of oRange.Information(6) to determine the page position. On my system, this gets reset properly after I insert the page break, but on their computer it just keeps incrementing, so it ends up telling me it’s 16” from the top of an 11” page.

The documentation from which I learned about the oRange.Information() method was written for Office 2000, so it should not be and issue of Office versions.

Anyone have a suggestion on how to fix this or know an alternate workaround for handling the page breaks?

See also:
Outlook Automation (has external links to MS Outlook Automation samples)
Excel Automation
Contributors: Alex Feldstein Del Lee tTreyWalpole John Koziol FredArmoni Carl Karsten
Category Code Samples Category Automation
( Topic last updated: 2007.08.03 07:36:43 PM )