Does anyone have a solution to the problem of properly exporting FoxPro memo
fields to Excel ? Either a) the memo fields just don't appear in Excel or
b) they are truncated at 255 characters.

It's been a pet peeve of mine over the years, and it seems Microsoft does
nothing to address the issue.

Thanks,

Paul

Paul,
The best available technique for the exporting memo fields (at least that I've found) is the code in http://support.microsoft.com/default...;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.

Alternately, you could automate Excel and directly load the data into the spreadsheet.

Rick

"Paul" <paule (AT) mindspring (DOT) com> wrote

Hello, Paul, Rick!
Rick wrote on Mon, 15 Sep 2003 09:45:03 -0400:

RB> Alternately, you could automate Excel and directly load the data into
RB> the spreadsheet.

Here's some code that I found in my archives. Originally posted to
microsoft.public.fox.programmer.exchange by Cindy Winegarden.

<vfp_code>
*!* PROCEDURE MakeSpreadsheet
*!* Note to me: CHR([A]) = 65
*!* If fieldname contains pct or percent, will be formatted as a percent.

PARAMETERS lcSaveFileName, lnDataStartRow

MyData = ALIAS()

oExcel = CREATEOBJECT([Excel.Application])
oExcel.Visible = .T.

WITH oExcel

..Workbooks.Add
..ActiveWorkbook.Sheets(1).Activate

*!* Get Array of field names from my table called "MyData"
SELECT (MyData)
= AFIELDS(laTempArray)
lnNumberOfColumns = ALEN([laTempArray], 1)

*!* Put Column heading field names in first row
*!* Something could be done with the captions as column headers
FOR lnColumnCount = 1 TO lnNumberOfColumns
.Cells(1, lnColumnCount).Value = PROPER(laTempArray(lnColumnCount, 1))
ENDFOR

*!* Bold the first row (Column headings) - looks nice
..Rows("1:1").Select
..Selection.Font.Bold = .T.

*!* Now copy data, one row at a time
*!* SELECT (MyData)
lnRowNum = 0
SCAN
COPY TO ARRAY laMyRow NEXT 1
FOR lnColumnCount = 1 TO lnNumberOfColumns
*!* Trim the character stuff to prepare for AutoFit
IF TYPE([laMyRow(lnColumnCount)]) = [C]
laMyRow(lnColumnCount) = ALLT(laMyRow(lnColumnCount))
ENDIF
*!* Fix blank dates which cause errors
IF TYPE ([laMyRow(lnColumnCount)]) = [D] AND laMyRow(lnColumnCount) = {}
laMyRow(lnColumnCount) = []
ENDIF
*!* Insert data starting in lnDataStartRow
.Cells(lnRowNum + lnDataStartRow, lnColumnCount).Value = ;
laMyRow(lnColumnCount)
*!* Re-format character-numbers like zipcodes from General to Text
IF TYPE([laMyRow(lnColumnCount)]) = [C]
.Cells(lnRowNum + lnDataStartRow, lnColumnCount).NumberFormat = [@]
ENDIF
*!* Re-format numbers
IF TYPE([laMyRow(lnColumnCount)]) = [N]
DO CASE
*!* Percentages to xxx.x%, depending on field name
CASE [PCT] $ UPPER(laTempArray(lnColumnCount, 1)) OR ;
[PERCENT] $ UPPER(laTempArray(lnColumnCount, 1))
.Cells(lnRowNum + lnDataStartRow, lnColumnCount).NumberFormat =
[0.0%]
OTHERWISE
.Cells(lnRowNum + lnDataStartRow, lnColumnCount).NumberFormat = [0.00]
ENDCASE
ENDIF
*!* Re-format Currency: ($1234.10)
IF TYPE([laMyRow(lnColumnCount)]) = [Y]
.Cells(lnRowNum + lnDataStartRow, lnColumnCount).NumberFormat = ;
["$#,##0.00_);($#,##0.00)"]
ENDIF
ENDFOR
lnRowNum = lnRowNum + 1
ENDSCAN

*!* Want to autofit all of the filled columns - looks nice
..Columns("A:" + _GetChar(lnNumberOfColumns)).EntireColumn.AutoFit

*!* Select first cell so it doesn't open up all black (selected)
..Range([A1]).Select

IF FILE((lcSaveFileName))
ERASE (lcSaveFileName)
ENDIF

..ActiveWorkbook.SaveAs((lcSaveFileName))
..Quit && Quits Excel, otherwise still open and invisible

ENDWITH

RELEASE ALL LIKE l*

*!* Return A, AA, BC etc notation for nth column
*!* Cetin Basoz, posted on UT, July, 1999
FUNCTION _GetChar
LPARAMETERS tnColumn && Convert tnValue to Excel alpha notation
IF tnColumn = 0
RETURN []
ENDIF
IF tnColumn <= 26
RETURN CHR(ASC([A]) - 1 + tnColumn)
ELSE
RETURN _GetChar(INT(IIF(tnColumn % 26 = 0, tnColumn - 1, tnColumn) /
26)) + ;
_GetChar((tnColumn - 1) % 26 + 1)
ENDIF
</vfp_code>
--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts - Powered By VFP8