Word automation for Invoices

[andre65]

Hello all,

In my current application I use a VFP report for printing invoices. This is a fixed report, the user cannot modify the layout. Now you know customers, they all have specific demands for invoice layout. So I thought creating a Word template would be nice. I know how to automate from VFP with Word, but for this specific case i was wondering if you guys already created something like this.

An invoice is like a header, body (mutiple line) and a footer. What is the best way to create this Word automation from VFP. Specificly the repeating body is exiting.

Thanks for any reply
André

 

[Mike Gagnon]

André

I noticed that most of the invoice templates that Microsoft uses are in Excel (which might be easier to automate). Have you considered using Excel?


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[andre65]

No I did not, and you are right, it might be easier to automate. What is the best way using Excell to find specific merge cells, a find and replace method (how to do that)? or predefine XY cells at user application level? What is an easy way to duplicate rows (for repeating invoice lines). What is an easy way to detect an overflow to print more than one page.

André

 

[Mike Gagnon]

Andre65

Too many questions!! How far did you get with it?. Here is how to open an existing workbook ()which I assume you will need since you are using a template).

Local loExcel,loWorkBook
loExcel = createobject('excel.application')
loWorkbook = loExcel.workbooks.open("c:\myInvTemplate.xls")
loExcel.visible = .t.

Start with that and come back if you have other difficulties.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

André,

Actually, my inclination would be to stay with Word. As invoices are customer-facing documents, you want the presentation and page layout to be as good as possible, and Word is better than Excel in that detartment.

I would insert the invoice headers and totals into the body of the document, and use a Word table for the invoice lines. That should give you the basics. I think you'll find it easier than Excel when it comes to things like deciding where to force a page break for multi-page invoices, and what information you want to appear on the continuation pages.

That said, if you feel comfortable doing it in Excel, don't let me discourage you.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[andre65]

I prefer using Word from enduser's point of view. So I created an invoice template in Word with a table for the invoice lines. I created a table with 2 rows, the 1st with 5 columns, 2nd with 1 column (for optional comments). Now when there are more than 1 invoice line, i want to copy the 2 rows and append them to the table. I can't make this work. Here's what i did:

loWord = CREATEOBJECT('word.application')
loDoc = loWord.documents.open('c:\invoice_template.doc')
loTable = loDoc.tables(1)
*-- copy template table
loTable.range.copy
*-- Move to the end of the table
loTable.range.moveEnd(wdTable)
*-- Paste the template invoiceline
loTable.range.paste
loDoc.saveAs('c:\my_invoice.doc')
loWord.quit
release loWord

Now the result seems to be OK, but ... the rows are not appended, but inserted before row 1.

I tried the same with the Selection object. The rows are appended but in a little strange way:
loWord = CREATEOBJECT('word.application')
loDoc = loWord.documents.open('c:\invoice_template.doc')
loTable = loDoc.tables(1)
loTable.select
*-- copy template table
loWord.selection.copy
*-- Move to the end of the table
loTable.select
loWord.selection.moveEnd(wdTable)
*-- Paste the template invoiceline
loWord.selection.paste
loDoc.saveAs('c:\my_invoice.doc')
loWord.quit
release loWord

Now ... what am i doing wrong.

Gr,
André

 

[JRB-Bldr]

While using WORD Automation within your VFP application is certainly a way to get your invoice document formatted, have you considered using VFP's Report and then using something like FRX2Any (

http://www.frx2any.com/

) to create your DOC file?

I am doing a similar process using the previous program FRX2Word and it is working for me well.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Originally I thought that FRX2Word was a predecesor to FRX2Any, but I might be wrong.

Regardless, if you are interested in FRX2Word go to:

http://wikis.com/wc.dll?Wiki~FRX2Word~VFP

Good Luck,
JRB-Bldr

 

[Mike Lewis]

JRB,

<< I thought that FRX2Word was a predecesor to FRX2Any >>

I can see why you would think that, but it's not so. FRX2WORD was written by John Koziol, who unfortunately was not able to support it (I think he went to work for Microsoft).

FRX2ANY is written by a Canadian outfit called Neova, and is much more of a commercial product.

Personally, I no longer use either of these, but have switched to XFRX, from

http://www.eqeus.com,

which, in my humble o., outshines both of them.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MarciaAkins]

Hello Andre.

I am using Word Automation to product product quotyes for one of my clients. I have a table called QuoteMap that contains the names of the bookmarks in the .dot file and information on how to replace the bookmark from the VFP data. It then goes on to insert detail lines using a table in another document. I hope that this helps give you some ideas:

loProdDoc = loWord.Documents.Add( FULLPATH( CURDIR() ) + [Quote\] + lcProductTemplate + [.Dot], .F., 0 )  
SELECT QuoteMap
SCAN FOR UPPER( ALLTRIM( TemplateName ) ) == UPPER( ALLTRIM( lcProductTemplate ) ) AND ;
  UPPER( ALLTRIM( PROCNAME ) ) == [PRODUCT]
    This.ReplaceBookMark( loWord )
ENDSCAN
*** now get all the line items for this product
*** First we need a reference to the table that they are
*** going to be listed in
loTable = loProdDoc.Tables[ 2 ]
SELECT lv_quote_item
lnRow = 2
SCAN
  *** Fill in the table with the order items
  WITH loTable
    WITH .Rows[ lnRow ]
      .Cells[ 1 ].Range.InsertAfter( TRANSFORM( lv_quote_item.qitem_count ) )
      lcUOM = IIF( SEEK( lv_Quote_Item.qitem_uom, [lookup_dtl], [ld_key] ), ;
        ALLTRIM( lookup_dtl.ld_code ), [] )
      .Cells[ 2 ].Range.InsertAfter( TRANSFORM( lv_quote_item.qitem_thick ) + [ ] + lcUOM )
      lyPrice = IIF( NOT EMPTY( lv_Quote_Item.fc_qitem_price ), lv_Quote_Item.fc_qitem_price, lv_Quote_Item.qitem_price )
        .Cells[ 3 ].Range.InsertAfter( TRANSFORM( lyPrice, [$$,$$$,$$9.99] ) + [ ] + lcCurrencySymbol )

      *** Now construct the thickness tolerance from the fields in lv_quote_item
      lcThickTol = TRANSFORM( lv_quote_item.thick_tol_min ) + [/] + TRANSFORM( lv_quote_item.thick_tol_max ) + [ ] + lcUOM
      .Cells[ 4 ].Range.InsertAfter( lcThickTol )
    ENDWITH
    *** Add a new row
    .Rows.Add()
    lnRow = lnRow + 1
  ENDWITH
ENDSCAN
*** Now remove the extra row that was added
loTable.Rows( lnRow ).Delete
*** Now copy and paste the product stuff into the main quote document
loWord.Selection.WholeStory()
loWord.Selection.Copy()
loProdDoc.Close( .F. )
loWord.Selection.Endkey( wdStory )
loWord.Selection.Paste()

Marcia G. Akins

http://www.TightlineComputers.com

 

[andre65]

Thank you for all your suggestions.

I'm working now on a Microft Word automation solution. I've been reading the VBAWRD.CHM visual basic word help and done some experimenting.
Now I think I have a pretty nice solution for creating an invoice template in word.

André