> On Mon, August 18, 2008 11:28 am, Allen wrote:
>> There were some write ups on vfug if I remember correct. www.vfug.org
>> Allen
>
>
> Good point! I think Matt Jarvis wrote a series of articles (or perhaps
> just one) on Office automation as well. Matt????
>
Clearly I need to put this stuff together in an organized way and make it
available to the world... I wrote this stuff years ago and still get asked
for it...
This one from the series I wrote doesn't specifically address the column
formatting that was asked about (someone already addressed that) but maybe
there's a nugget in here to take it the next step... see below.
--
Matt Jarvis
Eugene, Oregon USA
A Basic Introduction to Office Automation using MS Visual FoxPro Part Three
Introduction
In the last installment in this series I supplied a program that
demonstrated a few of the concepts of Word automation in order to do a
mail merge. This was part of our overall scenario of getting out form
letters to customers based on their sales data from the Tastrade sample
data. Customers that met or exceeded the sales levels got a nice warm and
fuzzy letter, those that didnt got a nasty-gram telling that they need to
get busy if they want to take advantage of price discounts.
This month well take the customer sales data and make it into a pretty
chart to be included in the letter we send. Since this is a series on
Office automation, well be using MS Excel as our spreadsheet and charting
program.
Our program will continue from where the last one left off. In other
words, this months supplied program is also last months, with the
additional Excel functionality occurring at the end.
Okay, so what can we do with Excel through automation? Lots of things,
actually
. remember that VFP Reports dont lend themselves well to
emailing or playing nicely with other types of output. Along comes Word or
Excel automation and you can use the full power of Word or the
mathematical prowess of Excel to create some very heavy duty reports. [See
the next installment of this series on bring all of this together. Well
be emailing VFP output using Word and Excel to generate the output instead
of typical FRXs
.]
Lets have some fun
.
You can copy the following text into a PRG file and step through it if you
like
* - Begin line by line examples
#include msword.h
#include excel9.h
*(In case you dont want to use the .H files, here are the constants:
#DEFINE xlNone -4142
#DEFINE xlContinuous 1
#DEFINE xlThick 4
#DEFINE xlAutomatic -4105
* XlBordersIndex
#DEFINE xlInsideHorizontal 12
#DEFINE xlInsideVertical 11
#DEFINE xlDiagonalDown 5
#DEFINE xlDiagonalUp 6
#DEFINE xlEdgeBottom 9
#DEFINE xlEdgeLeft 7
#DEFINE xlEdgeRight 10
#DEFINE xlEdgeTop 8
* - lets watch each line run
set step on
* - add a new sheet, fill in some data
oExcel = createobject( 'excel.application')
oExcel.visible = .t.
oExcel.Workbooks.add()
oWorkbook = oExcel.Worksheets(1)
* - give the workbook tab a name
oWorkBook.Name = "VFUG Automation Example"
oWorkbook.Cells( 1, 1) = 4
oWorkbook.Cells( 2, 1) = 3
oWorkbook.Cells( 3, 1) = 2
oWorkbook.Cells( 4, 1) = 7
oWorkbook.Cells( 5, 1) = 5
oWorkbook.Cells( 1, 2) = 41
oWorkbook.Cells( 2, 2) = 31
oWorkbook.Cells( 3, 2) = 21
oWorkbook.Cells( 4, 2) = 71
oWorkbook.Cells( 5, 2) = 51
* - sort the data in the column
oWorkBook.Columns("A:A").Select
oExcel.Selection.Sort( oexcel.Range("A1"))
if .f.
* - add a formula to add the two columns, doing it the hard way
oWorkBook.Range("C1").Select
oExcel.ActiveCell.FormulaR1C1 = "=+RC[-2]+RC[-1]"
oWorkbook.Range("C1").Select
oExcel.Selection.Copy
oWorkbook.Range("C2").Select
oExcel.ActiveSheet.paste
oWorkbook.Range("C3").Select
oExcel.ActiveSheet.paste
oWorkbook.Range("C4").Select
oExcel.ActiveSheet.paste
oWorkbook.Range("C5").Select
oExcel.ActiveSheet.paste
endif
* - or the easy way
oWorkBook.Range("C1").Select
oExcel.ActiveCell.FormulaR1C1 = "=+RC[-2]+RC[-1]"
oWorkBook.Range("C1").Select
oWorkBook.Range("C1:C5").Select
oWorkbook.Range("C1").Select
oExcel.Selection.Copy
oWorkBook.Range("C1:C5").Select
oExcel.ActiveSheet.Paste
* - let's add formulas to sum the columns
oWorkBook.Range("A7").Select
oExcel.ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)"
oWorkBook.Range("A8").Select
oWorkBook.Range("B7").Select
oExcel.ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)"
oWorkBook.Range("B8").Select
oWorkBook.Range("C7").Select
oExcel.ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)"
oWorkBook.Range("C8").Select
* - let's add an actual formula and retrieve the results
* of the calculation
oWorkbook.Cells( 10, 1) = "Principal"
oWorkbook.Cells( 11, 1) = "Down Payment"
oWorkbook.Cells( 12, 1) = "Interest Rate"
oWorkbook.Cells( 13, 1) = "Term (Months)"
oWorkbook.Cells( 14, 1) = "Monthly Payment"
oWorkbook.Cells( 10, 2) = 150000
oWorkbook.Cells( 11, 2) = 5000
oWorkbook.Cells( 12, 2) = 6
oWorkbook.Cells( 13, 2) = 360
oWorkbook.Cells( 14, 2) = "=PMT(B12/12/100,B13,B10-B11)"
*!* oWorkbook.Cells.Select
*!* oWorkbook.Range("C8").Activate
*!* oExcel.Selection.Columns.AutoFit
* - let's make it a bit prettier to look at
oWorkbook.Cells.Select
oExcel.Selection.Columns.AutoFit
oWorkbook.Range("A1").Select
* - what is the monthly payment with a $5k down payment?
? oWorkbook.Cells( 14, 2).Value
* - how about with a $25k down payment?
oWorkbook.Cells( 11, 2) = 25000
? oWorkbook.Cells( 14, 2).Value
* - for grins, let's play with the borders of the Payment Calculator
oWorkbook.Range("A10:B14").Select
oExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
oExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With oExcel.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
EndWith
With oExcel.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
EndWith
With oExcel.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
EndWith
With oExcel.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
EndWith
oExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
oExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
oWorkbook.Range("A1").Select
* - End line by line examples
NOTE: The example programs used in this series are written using VFP6 on a
WIN2K machine, along with MS Office 2000 SR-1. You may want to make sure
that your MS Office package is at least at this level by going to:
http://office.microsoft.com/ProductUpdates/default.aspx
The download files that accompany this article are located here: (BARBARA
- PLEASE PUT URL HERE). To run the example, first download the zip file
and extract it into a directory called C:\vfug_automation. (The directory
needs to be explicitly named like this for the mail merge data source to
work properly sorry!).
The source code consists of 3 files:
automation_example2.prg
Excel9.h
msword.h
To run the example program, start VFP and set your default directory to
C:\VFUG_Automation. Then DO automation_example2 to run the example.
Please refer to the PRG file called Automation_Example2.prg.
The examples above and the program I provided really only scratch the
surface of what you can do with Excel through automation. If time allows,
Ill do a column on advanced techniques and well really put Excel through
its paces!
About the Author:
Matthew Jarvis is a Microsoft Certified Professional and resides in
Eugene, Oregon, where he works as the Technical Director for a local
software development company. He specializes in VFP-centric applications
and web integration. He can be reached at [EMAIL PROTECTED] Also an award
winning homebrewer and author, he has written Brew Simple Software which
he markets at the www.brewdomain.com website.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
