Here is some code written by Çetin Basöz which I adapted to handle Nulls and a couple of other things which I can't remember.
Thanks to Çetin for the great routine!!
Kevin
**********************************
SELECT
** Optional: now let's go through the table and decide which columns
**are memo fields so we can make ajustments (further down in code) to the output
lnFieldcount = AFIELDS(laFields)
lnMemoFields=0
FOR i =1 to lnFieldcount
IF ALLTRIM(laFields(i,2))=="M"
lnMemoFields=lnMemoFields+1
DIMENSION laMemoFields(lnMemoFields)
laMemoFields(lnMemoFields)=i
ENDIF
ENDFOR
=zTable2ClipBoard() && This is the nifty routine Cetin wrote
** #include "vfp_excel.h"
oExcel = createobject("Excel.Application")
WITH oExcel
.visible = .t.
.Workbooks.Add
** If you wish, I go throught the 1st 26 coulmns and reset
** any fields that have memo fields to 40 characters and wrap the text
** Optional formating -- sort of brute force solution, oh well.
*******
** now start formatting the excel spreadsheet
WITH .ActiveWorkbook.ActiveSheet
.Paste
IF lnMemoFields>0
FOR j=1 to lnMemoFields
i=laMemoFields(j)
DO case
CASE i=1
.columns("A:A").Select
CASE i=2
.columns("B:B").Select
CASE i=3
.columns("C:C").Select
CASE i=4
.columns("D:D").Select
CASE i=5
.columns("E:E").Select
CASE i=6
.columns("F:F").Select
CASE i=7
.columns("G:G").Select
CASE i=8
.columns("H:H").Select
CASE i=9
.columns("I:I").Select
CASE i=10
.columns("J:J").Select
CASE i=11
.columns("K:K").Select
CASE i=12
.columns("L:L").Select
CASE i=13
.columns("M:M").Select
CASE i=14
.columns("N:N").Select
CASE i=15
.columns("O:O").Select
CASE i=16
.columns("P:P").Select
CASE i=17
.columns("Q:Q").Select
CASE i=18
.columns("R:R").Select
CASE i=19
.columns("S:S").Select
CASE i=20
.columns("T:T").Select
CASE i=21
.columns("U:U").Select
CASE i=22
.columns("V:V").Select
CASE i=23
.columns("W:W").Select
CASE i=24
.columns("X:X").Select
CASE i=25
.columns("Y:Y").Select
CASE i=26
.columns("Z:Z").Select
ENDCASE
.cells.ColumnWidth = 40
.cells.WrapText = True
ENDFOR
ENDIF (lnMemoFields>0)
******** end optional formatting
.Range("A1").Select
.Cells.Select
.Cells.Columns.AutoFit
ENDWITH
ENDWITH
************************************
*!* FUNCTION zTable2ClipBoard
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 ix
*!* LPARAMETERS tnField
lcType = type(field(ix))
IF lcType = "G"
lcField = field(ix)
RETURN transform(&lcField)
ENDIF
luValue = eval(field(ix))
DO case
case isnull(luValue) && added due to error in Oracle with null fields
lcValue=""
CASE lcType = "D"
lcValue = dtoc(luValue)
CASE lcType = "T"
lcValue = ttoc(luValue)
CASE lcType $ "NY"
lcValue = padl(luValue,20," ")
CASE lcType = "L"
lcValue = iif(luValue,"Yes","No")
CASE lcType $ "M" && Replace paragraph marks with "~"
lcValue = strtran(luValue, chr(13)+chr(10), space(5))
*!* lcValue = strtran(luValue, chr(13)+chr(10), "~")
*!* Now Replace enter chr(13) with some spaces to solve excel issue
luValue = lcValue
lcValue = strtran(luValue, chr(13), space(5))
CASE lcType $ "C"
lcValue = luValue
OTHERWISE
lcValue = ""
ENDCASE
RETURN alltrim(lcValue)
*!* Cetin
*!* Çetin Basöz
