excel automation 1

[fluppeke]

hey experts,

I have a form in which a file up a file
My boss asked be to make an excel file from this file with excel automation.
I pretty new in this thing so as many help as possible would be very helpfull
The file is stucture as follew

field a date field
field b N(10)
field c memo field
field d memo field

So I think the problem will be the memo fields
How can i get them in 1 cell of excel
Excel version 2000 or 2003
Vfp 7 sp1

many thanks

Filip Merlier

 

[bborissov]

CREATE CURSOR crsTest (Fld1 D, Fld2 N(10), Fld3 M, Fld4 M)
FOR asd = 1 TO 50
    INSERT INTO crsTest VALUES (DATE()+asd, asd,REPLICATE([A],asd),REPLICATE([B],asd))
NEXT



LOCAL oExcel AS Excel.Application
oExcel = CREATEOBJECT([Excel.Application])
oExcel.Visible = 1 
oExcel.WorkBooks.Add()
SELECT crsTest
lnRow = 0
SCAN
    lnRow = lnRow + 1
    FOR lnFor = 1 TO FCOUNT()
        oExcel.Cells(lnRow, lnFor).Value = EVALUATE(FIELDS(lnFor))
    NEXT
ENDSCAN
oExcel.ActiveWorkbook.SaveAs([c:\MyTest.XLS])
oExcel.Visible = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fluppeke]

Hey Boris,

I have tried this and it works fine when there is no cr of lf in the memo field.
How can I show how the putted it in without seeing the cr/lf

The key is the date so I would like to have a heading also per date

Is this difficult, or can you show me an excample of this.

many thanks

Filip Merlier

ps : how dou you create in tek-tips a box with code like you did ?

 

[bborissov]

#define CRLF CHR(13)+CHR(10)
CREATE CURSOR crsTest (Fld1 D, Fld2 N(10), Fld3 M, Fld4 M)
FOR asd = 1 TO 50
IF asd > 25
INSERT INTO crsTest VALUES (DATE()+asd, asd,REPLICATE([A],24)+CRLF+REPLICATE([D],asd-24),REPLICATE(,24)+CRLF+REPLICATE([F],asd-24))
ELSE
INSERT INTO crsTest VALUES (DATE()+asd, asd,REPLICATE([A],asd),REPLICATE(,asd))
ENDIF
NEXT

LOCAL oExcel AS Excel.Application
oExcel = CREATEOBJECT([Excel.Application])
oExcel.Visible = 1
oExcel.WorkBooks.Add()
DECLARE aHeaders[FCOUNT()]
FOR lnFor = 1 TO FCOUNT()
aHeaders[lnFor] = [Field ]+FIELDS(lnFor)
NEXT

*** First row will contain headers
FOR lnFor = 1 TO FCOUNT()
oExcel.Cells(1, lnFor).Value = aHeaders[lnFor]
NEXT

SELECT crsTest
lnRow = 1 && We start from second row, becuase first is header row
SCAN
lnRow = lnRow + 1
FOR lnFor = 1 TO FCOUNT()
IF TYPE(FIELDS(lnFor)) $ [CM] && Char or memo
oExcel.Cells(lnRow, lnFor).Value = STRTRAN(EVALUATE(FIELDS(lnFor)),CHR(13),[]) && Remove CR
ELSE
oExcel.Cells(lnRow, lnFor).Value = EVALUATE(FIELDS(lnFor))
ENDIF
NEXT
ENDSCAN
oExcel.DisplayAlerts = .f. && Do not display wanings
oExcel.ActiveWorkbook.SaveAs([c:\MyTest.XLS])
oExcel.Visible = 1
[/code]

To create a code block you need to round the block with
[ code ] [ / code ] tag (w/o spaces) like this


[ code ]
Your code block here
[ /code ]
again w/o spaces.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

WOW! Here the code better formated

#define CRLF CHR(13)+CHR(10)
CREATE CURSOR crsTest (Fld1 D, Fld2 N(10), Fld3 M, Fld4 M)
FOR asd = 1 TO 50
    IF asd > 25
       INSERT INTO crsTest VALUES (DATE()+asd, asd,REPLICATE([A],24)+CRLF+REPLICATE([D],asd-24),REPLICATE([B],24)+CRLF+REPLICATE([F],asd-24))
    ELSE
       INSERT INTO crsTest VALUES (DATE()+asd, asd,REPLICATE([A],asd),REPLICATE([B],asd))
    ENDIF
NEXT

LOCAL oExcel AS Excel.Application
oExcel = CREATEOBJECT([Excel.Application])
oExcel.Visible = 1
oExcel.WorkBooks.Add()
DECLARE aHeaders[FCOUNT()]
FOR lnFor = 1 TO FCOUNT()
    aHeaders[lnFor] = [Field ]+FIELDS(lnFor)
NEXT

*** First row will contain headers
FOR lnFor = 1 TO FCOUNT()
    oExcel.Cells(1, lnFor).Value = aHeaders[lnFor]
NEXT

SELECT crsTest
lnRow = 1 && We start from second row, becuase first is header row
SCAN
    lnRow = lnRow + 1
    FOR lnFor = 1 TO FCOUNT()
        IF TYPE(FIELDS(lnFor)) $ [CM] && Char or memo
           oExcel.Cells(lnRow, lnFor).Value = STRTRAN(EVALUATE(FIELDS(lnFor)),CHR(13),[]) && Remove CR
        ELSE
           oExcel.Cells(lnRow, lnFor).Value = EVALUATE(FIELDS(lnFor))
         ENDIF
    NEXT
ENDSCAN
oExcel.DisplayAlerts = .f. && Do not display wanings
oExcel.ActiveWorkbook.SaveAs([c:\MyTest.XLS])
oExcel.Visible = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fluppeke]

Boris,

Thank You very much.
Life can be easy with such good fellow workers and tek-tips

Filip Merlier

 

[baltman]

Export a Formatted Table to Excel using HTML
faq184-4704

You can customize to suit your needs. It is much faster than cell-by-cell automation.

Brian