I have a VFP8 form that has a command button with code to convert a VFP8 free table to an Excel 2007 XLS file.
My two date columns are always in column 6 and column 7 each time that the command button is clicked to create the Excel file.
I've noticed that my date fields in this converted Excel 2007 file have ######### in the Excel date fields instead of the date in this format, *3/14/2001.
What's odd about this date problem is that the first 24 rows of the Excel file will have the date in the correct *3/14/2001 format, and then at the 25th row the date fields will switch to being all ####'s.
I have two memo fields that get converted to this Excel file as well, so I don't know if the memo fields are causing this date field problem or not.
I sometimes have a carriage return(enter key) in one of the memo fields or a # character in the memo field, so perhaps VFP is seeing these "enter" key's or #'s as a command to mess up the date fields.
I recorded an Excel macro in which I formatted the two columns of the converted Excel date fields so that all of the rows of dates will be in the *3/14/2001 format.
Here's a copy of that Excel 2007 macro:
=============================================================
Sub VFP1()
'
' VFP1 Macro
'
'
Columns("F:F").Select
Range("F4").Activate
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").Select
Range("G4").Activate
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll Down:=99
End Sub
==============================================================
I need to know how to put these Excel macro lines of code into VFP8 code.
Here's my complete code for the command button on my VFP8 form, note: I remarked out, *, the lines of code that I tried and couldn't get to work properly or didn't want to use:
==============================================================
ldFrom = CTOD(thisform.text1.Value)
ldTo = CTOD(thisform.text2.Value)
IF DTOC(ldFrom)=" / / "
MESSAGEBOX("Please type in a starting date for the report!" + CHR(13) +;
" Please try again.",0+48,thisform.caption)
THISFORM.REFRESH
RETURN 0
ENDIF
IF DTOC(ldTo)=" / / "
MESSAGEBOX("Please type in a stopping date for the report!" + CHR(13) +;
" Please try again.",0+48,thisform.caption)
THISFORM.REFRESH
RETURN 0
ENDIF
SELECT Eiu,Non_eiu_no,Barcode_no,Descrip,Time,Date_in,Date_out,problem,solution,Tkt_number,;
Department,Brand,Model_no,Equip_type,Locin;
FROM C:\foxpro1\data\maint.dbf ;
WHERE BETWEEN(date_out,ldFrom,ldTo);
INTO CURSOR mytest
SELECT MyTest
COPY TO C:\Fox2Excel\Book1.dbf
DO c:\foxpro1\progs\fox2excel2.prg
#Define xlTop -4160
Local oExcel
oExcel = Createobject("Excel.Application")
* Normally I wouldn't use these
* just trying to match your code a bit
*Local lcColumn, lcColumnExpression, lcFmtExp
*lcColumn = 'C'
*lcColumnExpression = m.lcColumn+':'+m.lcColumn
*lcFmtExp = "yyyy/mm/dd;@"
With oExcel
.WorkBooks.Add
.Visible = .T.
SET DEFAULT TO c:\fox2excel
VFP2Excel('book1.dbf',;
'select eiu,non_eiu_no,barcode_no,descrip,time, date_in,date_out,;
CAST(CHRTRAN(problem,CHR(13)+CHR(10),"") as M) as problem,;
CAST(CHRTRAN(solution,CHR(13)+CHR(10),"") as M) as solution,Tkt_number,department,brand,;
model_no,equip_type,locin from book1',;
.ActiveWorkBook.ActiveSheet.Range('A1'))
* This part is cosmetic
*With .ActiveWorkBook.ActiveSheet
*oExcel.ActiveWorkBook.ActiveSheet.range("A1").NumberFormat = "mm/dd/yyyy"
*Columns("F:F").Select
*Selection.NumberFormat = "m/d/yyyy"
*Columns("G:G").Select
*Selection.NumberFormat = "m/d/yyyy"
*.UsedRange.VerticalAlignment = xlTop && set all to top
*With .Columns(5)
*.ColumnWidth = 80 && 80 chars width
*.WrapText = .T.
*Endwith
*.UsedRange.Columns.AutoFit
*.UsedRange.Rows.AutoFit
*ENDWITH
&& The new workbook gets renamed using the memory variable;
&& "m.fileName" that was created in the line
&& of code directly below this line.
fileName = FORCEPATH(CMONTH(m.ldFrom)+ LTRIM(STR(YEAR(m.ldFrom)))+ '.xls', 'C:\Fox2Excel\Spreadsheets')
if val(oExcel.Version) > 11
oExcel.ActiveWorkBook.ActiveSheet.SaveAs(m.fileName, 56)
&& xlExcel8
else
oExcel.ActiveWorkBook.ActiveSheet.SaveAs(m.fileName)
oExcel.ActiveWorkbook.Saved = .T.
ENDIF
oExcel.quit() && Quits the Excel program.
Endwith
SET DEFAULT TO c:\fox2excel\spreadsheets
MESSAGEBOX("The Visual Foxpro Monthly Report was converted to an Excel spreadsheet." + CHR(13) +;
"The Excel spreadsheet that was created can be found in the following folder:" + CHR(13);
+ "C:\Fox2Excel\Spreadsheets!",0+64,thisform.caption)
THISFORM.REFRESH
SET DEFAULT TO c:\fox2excel
DELETE FILE c:\fox2excel\book1.dbf
DELETE FILE c:\fox2excel\book1.fpt
RETURN 1
==============================================================
Any help with this problem would be appreciated.
Thanks.
