VFP created XLS zeros trimed off?

W

Wilson Pye

Hi,

I am creating an XLS file from VFP 6.0. Excel reads the file OK except for
cutting off the leading zero in the zipcode col.

My dbf file contains a character field for zipcode.

I am using Excel97 no plroblem with leading zeros, the client is using
Excel2000 v9,0.419 and is losing the zero.

Below is the code I am using to produce the spreadsheet.
I would like to be able to create an XLS file from VFP that is compatable
with the newer version of Excel.



Code as follows:
*******************
SELECT exportcontacts
dbf2xl("exportcontacts")
***********************
PROCEDURE dbf2xl
LPARAMETERS lcDbfName
LOCAL oTemp, oXlApp, oSheet, lnCols, lnColCount, lnRec, lcXlName

IF DIRECTORY('c:\tournakit\export') && directory exists?
lcXlName = 'c:\tournakit\export\contacts.xls'
ELSE
WAIT WINDOW "ERROR: Contact Technical Support (ref Export failure
contacts - dbf2xl) Hit any key to continue......"
ENDIF

IF FILE(lcXlName)
ERASE (lcXlName)
ENDIF

SELECT (lcDbfName)

AFIELDS(aFlds, lcDbfName) && get field info into aFlds array
oTemp = GETOBJECT('', 'excel.sheet') && see if we can get an xl sheet
object

IF TYPE('oTemp') == 'O' AND !ISNULL(oTemp) && ok got it
oXlApp = oTemp.APPLICATION && create an xl app object
RELEASE oTemp

oXlApp.WorkBooks.ADD() && add a new workbook

* turn off calculations etc - saves time in populating the sheet
* can calculate manually later if necessary
oXlApp.Calculation = -4135 && xlCALCULATIONMANUAL
oXlApp.CalculateBeforeSave = .F.
oXlApp.ActiveWorkBook.UpdateRemoteReferences = .F.
oXlApp.ActiveWorkBook.PrecisionAsDisplayed = .F.
oXlApp.ActiveWorkBook.SaveLinkValues = .F.

oSheet = oXlApp.ActiveSheet && get the active sheet into an object

lnCols = FCOUNT(lcDbfName) && number of columns

oXlApp.VISIBLE = .F.

FOR lnColCount = 1 TO lnCols
* put field names into row 1
oSheet.Cells(1, lnColCount).VALUE = aFlds[lnColCount, 1]
ENDFOR
SELECT (lcDbfName)

lnRec = 1
* Ok, now from row 2, populate the spreadsheet with the table values
SCAN
lnRec = lnRec + 1
FOR lnColCount = 1 TO lnCols
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
ENDFOR
ENDSCAN

oSheet.UsedRange.COLUMNS.AutoFit

* Note: vfp6 doesn't do well sometimes with xl version higher than xl95
IF VAL(LEFT(ALLTRIM(oXlApp.VERSION), 1)) > 5 && save as version xl95
oXlApp.ActiveWorkBook.SAVEAS(lcXlName, 39) && 39 = xlEXCEL5
ELSE
oXlApp.ActiveWorkBook.SAVEAS(lcXlName, -4143) && -4143 = xlNORMAL
ENDIF

oXlApp.ActiveWorkBook.CLOSE(.F.)
oXlApp.QUIT()
RELEASE oSheet, oXlApp && not technically necessary
ENDIF

WAIT CLEAR
RETURN
***********

Thanks

Wilson Pye
 
C

Cindy Winegarden

Hi Wilson,

See if this does what you want -
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
Click to expand...

cFieldType = aFlds[lnColCount, 2]

If cFieldType = "C"
oSheet.Cells(lnRec, lnColCount).NumberFormat = "@"
* -or- oSheet.Cells(lnRec, lnColCount).NumberFormat = "00000"
EndIf
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
Click to expand...


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com


Wilson Pye said:
I am creating an XLS file from VFP 6.0. Excel reads the file OK except
for
cutting off the leading zero in the zipcode col.

My dbf file contains a character field for zipcode.

* Ok, now from row 2, populate the spreadsheet with the table values
SCAN
lnRec = lnRec + 1
FOR lnColCount = 1 TO lnCols
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
ENDFOR
ENDSCAN
Click to expand...
 
W

Wilson Pye

Hi Cindy,

Thanks for the code.
Not sure if you want me to add your code where you have it or replace some
existing code?

Thanks again

Wilson



Cindy Winegarden said:
Hi Wilson,

See if this does what you want -
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
Click to expand...

cFieldType = aFlds[lnColCount, 2]

If cFieldType = "C"
oSheet.Cells(lnRec, lnColCount).NumberFormat = "@"
* -or- oSheet.Cells(lnRec, lnColCount).NumberFormat = "00000"
EndIf
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
Click to expand...


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com


Wilson Pye said:
I am creating an XLS file from VFP 6.0. Excel reads the file OK except
for
cutting off the leading zero in the zipcode col.

My dbf file contains a character field for zipcode.

* Ok, now from row 2, populate the spreadsheet with the table values
SCAN
lnRec = lnRec + 1
FOR lnColCount = 1 TO lnCols
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
ENDFOR
ENDSCAN
Click to expand...
Click to expand...
 
C

Cindy Winegarden

Hi Wilson,

You need to format the cell before you set its value so you'll run both your
code and my code. You could move it to inside the IF !EMPTY(xVal) block if
it works better there for you.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com


Wilson Pye said:
Hi Cindy,

Thanks for the code.
Not sure if you want me to add your code where you have it or replace some
existing code?

Thanks again

Wilson



Cindy Winegarden said:
Hi Wilson,

See if this does what you want -
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
Click to expand...

cFieldType = aFlds[lnColCount, 2]

If cFieldType = "C"
oSheet.Cells(lnRec, lnColCount).NumberFormat = "@"
* -or- oSheet.Cells(lnRec, lnColCount).NumberFormat = "00000"
EndIf
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
Click to expand...


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
(e-mail address removed) www.cindywinegarden.com


Wilson Pye said:
I am creating an XLS file from VFP 6.0. Excel reads the file OK except
for
cutting off the leading zero in the zipcode col.

My dbf file contains a character field for zipcode.

* Ok, now from row 2, populate the spreadsheet with the table values
SCAN
lnRec = lnRec + 1
FOR lnColCount = 1 TO lnCols
xVal = EVAL(aFlds[lnColCount, 1]) && don't want to eval twice
* empty dates can be a problem so don't bother trying to fill
* cells with empty dates
IF !EMPTY(xVal)
oSheet.Cells(lnRec, lnColCount).VALUE = xVal
ENDIF
ENDFOR
ENDSCAN
Click to expand...
Click to expand...
Click to expand...
 
Top