locked
Help with converting Excel 2007 macro code for formatting date columns to VFP8 code RRS feed

  • Question

  • 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.

     

     

    Thursday, September 15, 2011 4:23 PM

Answers

  • I figured out the answer to my own problem and I thought that I'd share the solution with anyone else that had the same problem that I did.

    I added these lines of VFP code to my "Convert" command button code:

    * This part is cosmetic

    With .ActiveWorkBook.ActiveSheet

            oExcel.ActiveWorkBook.ActiveSheet.Columns("F:G").NumberFormat = "mm/dd/yyyy"
            
    Endwith

    I now have my two columns of date fields looking like this format, *3/14/2001.

    My form was a simple form with two text boxes that I would type in the starting and stopping date for the conversion of the free table to an Excel spreadsheet.

    The form also had two command buttons, one command button to do the conversion of the free table to an Excel spreadsheet and the other command button to quit the form and return to the form that this form was called from.

    Here's my entire code for the "Convert" command button: Note: I deleted all of the lines of code that I 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")
            
    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.Columns("F:G").NumberFormat = "mm/dd/yyyy"
            
    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




    • Marked as answer by HBrown1953 Friday, September 16, 2011 5:30 PM
    Friday, September 16, 2011 5:29 PM