We help IT Professionals succeed at work.

Export Memo field to excel

RVL
RVL asked
on
Hi
We are wondering if we can copy a memo field to excel. I did try this but no go.
copy to memo fields lond_memo for ima_ctrl = '125' type xl5

Thanks
Comment
Watch Question

Founder, Software Engineer, Data Scientist
Commented:
You need to use automation to do this.

xlapp = CREATEOBJECT("Excel.Application")
xlapp.Visible = .T.
xlworkbook = xlapp.WorkBooks.Add()
xlsheet = xlworkbook.Sheets(1)
SELECT table
SCAN ALL
    xlsheet.Cells(RECNO(),1).Value = table.memo
ENDSCAN
Does your question mean the solution from http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_26236996.html does not work?
Olaf DoschkeSoftware Developer
Commented:
Why do you accept the solution to use Craig Boyds Excel Export functions and then don't use them?

COPY TO is not able to export memo field, even if you specify them in a fields clause it does not regard them valid export fields, even if their content is short. Simple proof of not working concept:

CREATE CURSOR curTEST (amemo M)
APPEND BLANK
COPY TO some.xls FIELDS amemo TYPE XL5

Will invoke the error "no fields found to process", as COPY TO TYPE XL5 or XLS does not process memo fields.

Bye, Olaf.
RVL

Author

Commented:
long week
Olaf DoschkeSoftware Developer

Commented:
IF you want to use COPY TO, then first select into a cursor with no memo fields. Eg if the memo fields do only have up to 254 chars in them you can do:

SELECT LEFT(somememofield,254) as charfield FROM sometable into cursor someexportcursor

And then use COPY TO on that cursor.

Then automate excel to read that XLS in and save in new version format. Or use an OLEDB Provider to create/write to XLS or use automation or export to Office/Excel XML format. Many ways to do the same stuff.

If you downloaded the VFPExcel2007Functions.zip you want to call the CopyToExcel() function in this prg. If you read the comments you know it depends on the OLE DB provider you can download at http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

The function goes the route of using an OLE DB provider to create and write to an xls/xlsx/xlsm or xlsb file, depending on what file name you pass in. It's a very good solution as it does not depend upon excel itself being installed and it does not limit you to short texts in the memo fields.

Bye, Olaf.