how to export a meno field to Excel together with other fields ? 1

[pxw]

Following is the codes I want to export the contents of a memory field to a Excel file together with other fields. It doesn't work.

select service
COPY TO &gcExcelFile TYPE XLS FIELDS JobNo,comments &&comment is a memo field of service.dbf


Any help would be appreciated.


Peter

 

[baltman]

If you want to get a memo field into Excel, I'm assuming each record's memo data it isn't that large. If the length is under 250 characters, I'd add a text field and then use the relace all command, and then export it as you have been.

If if is larger than that you may have to look into driving Excel using using something like this...I've never tried this on a memo field, but I don't see why it wouldn't work...

Brian

&&&&&&&&&&&&&&&&&&&&&&&&&&

#DEFINE False .F.
#DEFINE True .T.
#DEFINE RPT_FONTNAME "Ariel"
#DEFINE RPT_FONTSIZE 8

*-- First, open the database and table and run the report

use service
copy fields JobNo,comments to temptable

CLOSE DATA ALL
use temptable

LOCAL loExcel, lcOldError, lcRange, lnSheets, lnCounter

WAIT WINDOW NOWAIT "Starting Excel..."

lcOldError = ON("ERROR&quot
ON ERROR loExcel = .NULL.
loExcel = GetObject(, "Excel.Application&quot
ON ERROR &lcOldError

IF ISNULL(loExcel)
loExcel = CreateObject( "Excel.Application" )
ENDIF

WITH loExcel
.Workbooks.Add
.DisplayAlerts = False

*-- Deleting all but one sheet.

lnSheets = .Sheets.Count
FOR lnCounter = 1 TO lnSheets - 1
.Sheets(1).Delete
ENDFOR

.ActiveWindow.DisplayGridlines = False

WAIT WINDOW NOWAIT "Building Header Rows"

WITH .Range("B1&quot
.Value = "Balance Report" && make var off of form
WITH .Font
.Bold = .T.
.Size = 14
ENDWITH
ENDWITH

*-- Center A1 over columns
With .Range("B1:F1&quot
.WrapText = False
.HorizontalAlignment = 3
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
.Merge
EndWith

*-- Now do the column headers

.Range("A1&quot.Value = "JobNo"
.Range("B1&quot.Value = "Comments"

lcRange = "A1:B1"

WITH .Range(lcRange)
.HorizontalAlignment = 3
.WrapText = True
.Font.Bold = .T.
.Font.Size = RPT_FONTSIZE
.Font.Name = RPT_FONTNAME
ENDWITH

*-- Now, scan through the table and put all
*-- the information in the spreadsheet

WAIT WINDOW NOWAIT "Populating cells:"

select temptable
GO TOP

*-- Populate the report

lnRow = 2
SCAN
WAIT WINDOW NOWAIT "Populating cells: Record " + ALLTRIM(STR(RECNO()))+" of " + ALLTRIM(STR(RECCOUNT()))
*-- Read the record into the cells
.Cells(lnRow, 1).Value = temptable.JobNo
.Cells(lnRow, 2).Value = temptable.comments
lnRow = lnRow + 1
ENDSCAN

WITH .Sheets(1)
.Select
.Name = "Job_Comments"
ENDWITH

WITH .sheets(1).pagesetup
.PrintArea = ("$A$1:$S$"+ ALLT(STR(lnRow)))
.Orientation = 2
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
ENDWITH

.Columns("A:B&quot.EntireColumn.AutoFit

ENDWITH

WAIT CLEAR

loExcel.Visible = .T.
Release loExcel
CLOSE DATA ALL

RETURN

 

[pxw]

hi Brian,
Thanks for your quick response and your codes. In my case, the length of the memo field is longer than 250. I will try your codes shortly and let you know the outcome.


Peter

 

[pxw]

hi Brian,
Your codes work very well! Only one question,
Can Excel be closed automatically after the export is completed ?



Peter

 

[baltman]

Glad it works... I quickly adapted some of my existing code and did't test it out or anything...

I'm sure that it can automatically save/quit, but I don't know exactly how... you probably want to issue some sort of "save as" and "quit" commands before the release command.

I am sure someone else knows...

Brian