how to i export memo fields to excel 1

[kosta]

Hi All,
we can export the cursor or table data's
with "export to xls..." command but memo fields is different how can i do this ?

TIA Soykan OZCELIK
Comp.Prog / MSFoxPro Programmer Developer

 

[rgbean]

Soykan,
The best available technique for the exporting memo fields (at least that I've found) is the code in

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241424

- "HOWTO: Export Memo Fields with Other Field Types to a Text File (Q241424)". You could then have Excel import the text file.

Rick

 

[GriffMG]

Kosta,

The short answer is that you can't.

The long answer involves making a temp table with a character field instead of the memo, and exporting that.

The largest field size will be 254.


HTH



Regards

Griff
Keep [Smile]ing

 

[kosta]

Here is the solution ,

*PS : excel-all.h (Excel header file) if you have not open your Excel, Alt+f11, f2 shows oject browser. then find excel constant (which is begin xl ).

Use memotable && your table name or cursor

=Table2ClipBoard()

#include "excel-all.h"
oExcel = createobject("Excel.Application&quot
With oExcel
.Workbooks.Add
.visible = .t.
With .ActiveWorkbook.ActiveSheet
.Paste
.Range('A1').CurrentRegion.Replace("PMARK",""+chr(10)+"",xlPart,xlByRows, .F.)
Endwith
With .Selection
.ColumnWidth = 50 && Increase width for memo
.HorizontalAlignment = xlGeneral
* .VerticalAlignment = xlTop
.WrapText = .T.
Endwith
Endwith


Function Table2ClipBoard
lcTempFileName = "X"+sys(2015)+".tmp"
handle = fcreate(lcTempFileName) && Create a temp file
#Define TABULATE chr(9)
#Define NL chr(13)

For ix = 1 to fcount()
=fwrite(handle, field(ix))
If ix < fcount()
=fwrite(handle, TABULATE)
Endif
Endfor
=fwrite(handle, NL)
Scan && Start scan..endscan
For ix = 1 to fcount() && Write field values
=fwrite(handle, typeconvert(ix) )
If ix < fcount()
=fwrite(handle, TABULATE)
Endif
Endfor
=fwrite(handle, NL)
Endscan
lnSize=fseek(handle,0,2)
=fseek(handle,0,0)
_Cliptext = fread(handle, lnSize) && Read file to clipboard
=fclose(handle)
Erase (lcTempFileName)

Function typeconvert
Lparameters tnField
lcType = type(field(ix))
If lcType = &quot;G&quot;
lcField = field(ix)
* Return '' && VFP5 and 3
Return transform(&lcField) && VFP6 and up
Endif
luValue = eval(field(ix))
Do case
Case lcType = &quot;D&quot;
lcValue = dtoc(luValue)
Case lcType = &quot;T&quot;
lcValue = ttoc(luValue)
Case lcType $ &quot;NY&quot;
lcValue = padl(luValue,20,&quot; &quot
Case lcType = &quot;L&quot;
lcValue = iif(luValue,&quot;Yes&quot;,&quot;No&quot
Case lcType $ &quot;M&quot; && Replace paragraph marks with &quot;PMARK&quot;
lcValue = strtran(luValue, chr(13)+chr(10), &quot;PMARK&quot
Case lcType $ &quot;C&quot;
lcValue = luValue
Otherwise
lcValue = &quot;&quot;
Endcase
Return alltrim(lcValue)

Soykan OZCELIK
Comp.Prog / MSFoxPro Programmer Developer

 

[Mike Lewis]

Kosta,

Excellent code. Thanks for posting it.

As you know, the problem with Excel memo fields is not that they are restricted to 255 characters.They're not. But they are restricted to a single paragraph. In other words, you can't have a CHR(13) or CHR(10) in them.

So basically you need to strip out those characters, and replace them with something like a space, which is what you are doing.

I'm facing this same problem in my present application, but fortunately the client undersands the issues and is willing to accept that the memo won't have multiple paragraphs.
Mike Lewis
Edinburgh, Scotland

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

 

[kosta]

Thanks Mike,
i've also made this code custom class such as calling
from form command button or where from you want

after SQL or use & select mytable
thisform.makeexcel1.memo2excel()

its doing all.. itself

HTH Soykan OZCELIK
Comp.Prog / MSFoxPro Programmer Developer

 

[kazl]

Hi.

This old bit of code is OK for outputting smaller files but otherwise its probably too slow. Might give you an idea tho.
Try it on a small file first (there's no progress meter on the screen)

Kaz

*-* mdfile = the data file to output to Excel
*-* mxfile = the Excel file name to Save As
STORE SYS(5)+CURDIR() TO mcurdir
mdfile = ALLTRIM(GETFILE('DBF','File Name','OK',0,'Select Data File to Export'))
STORE SUBSTR(SYS(2015),3,8)+'.XLS' TO mxfile
USE &mdfile
oExcelSession=CreateObject(&quot;Excel.Application&quot
oExcelSession.Workbooks.Add
oExcelSession.Visible=.T.
*- insert column headings
oRange = oExcelSession.ActiveSheet.Range(&quot;A1:A1&quot
WITH oRange
FOR mxc = 1 TO FCOUNT()
.Columns[mxc].Value = FIELD(mxc)
ENDFOR
ENDWITH
*- insert the data for the whole file
STORE 0 TO mcount
SCAN
STORE mcount+1 TO mcount
store &quot;A&quot;+ltrim(str(mcount+1,6)) to mr
store mr+&quot;:&quot;+mr to mr
oRange = oExcelSession.ActiveSheet.Range(mr)
WITH oRange
FOR mxc = 1 TO FCOUNT()
STORE FIELD(mxc) TO mf
.Columns[mxc].Value = &mf
ENDFOR
ENDWITH
ENDSCAN

 

[Mike Lewis]

Kazl,

Your code looks good, but you are right that it will be slow.

In general, whenever you reference a COM property or method, there is a significant overhead. When you do it in a loop, as is the case here, that overhead could become noticeable.

Still, your approach is good, and it should work fine for small data.

Mike Lewis
Edinburgh, Scotland

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

 

[baltman]

My tables2htm procedure found in faq184-3005 will also export MEMO fields into Excel as part of its functionality.

Brian