Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Exporting memo fields to Excel
Message
From
15/11/2000 08:45:08
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
General information
Forum:
Visual FoxPro
Category:
Reports & Report designer
Miscellaneous
Thread ID:
00441850
Message ID:
00442049
Views:
20
>Is there an easy way to do this w/o using automation with Excel?

Michael,
AFAIK there is no way w/o some automation. However in the supplied sample actually only find&replace part is needed automation since instead of clipboard usage you could save the file as CSV and open in Excel. It's needed because if memo contains CRLF Excel treats it as end-of-record and starts a new row. Also in FoxPro any char field could contain CR and/or LF chars. Here in sample clipboard transfer is used but keep in mind that it's not applicable just as shown here if reccount() is high (don't know an exact limit but around 2-3 thousand and might be related with installed RAM). Also I don't know why but find&replace fails if memo content are long (limit ? not just via automation but even if done manualy from Excel).
Use employee
=Table2ClipBoard()
#include "xlconstants.h"
oExcel = createobject("Excel.Application")
With oExcel
  .Workbooks.Add
  .visible = .t.
  With .ActiveWorkbook.ActiveSheet
    .Paste
    .Range('A1').CurrentRegion.Replace("PMARK", ;
         ""+chr(10)+"",xlPart,xlByRows, .F.)
  Endwith
  For ix=1 to fcount()
    If type(field(ix))='M'
      With .Columns(ix)
        .ColumnWidth = 50 && Increase width for memo fields and set wrap on
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = .T.
      Endwith
    Endif
  Endfor
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
    lcField = field(ix)
    =fwrite(handle, strtran(transform(&lcField), chr(13)+chr(10), "PMARK") )
    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)
Cetin
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform