Yossi:

I just finished some Excel automation this yesterday and am copying four of the methods attached that do the Excel instantiation, copying one of the files, etc. Somewhere in this you may find something useful. My experience with Office automation using VFP is you need a good network connection to speed up all the Google searches for answers. They're there, you just have to be persistent. A couple of helpful hints:

(1) In Excel 2010 go to File / Options/ Customize ribbon and make sure "Developer" is enabled.
(2) In Excel go to the "Developer" tab, click on "Record Macro" and manually do what you want to automate.
(3) When finished click on "Stop Recording" then click "Visual Basic"
(4) A Microsoft VB for Applications window will open. Look under the "Modules" folder, poke around, you will find very useful code for the operation you were attempting. It may not work exactly as shown but you'll be pointed in the right direction.
(5) When debugging be sure to set the Excel property "Visible" to .T. so you can watch the sheet being built.

Good luck on what can be a pain in the a$$ task. There are other ways to export data to Excel but using automation is the only way I know to present the user with something that even approaches a finished task. Sorry if some of the code's a bit sloppy, toward the end I just wanted to finish leaving cleanup for the next time I look at the program - which will be too soon.

Scott

FUNCTION Copy2Excel_All_files
    ***********************************************************************************
    * Create spreadsheets
    ***********************************************************************************
    CPA0010frm.StatusMessage.Caption ="Preparing to create Excel worksheets "
    This.Copy2Excel_CreateExcelObject(  )
    This.Copy2Excel_file1_apchck01(  )
    This.Copy2Excel_file3_apmast01(  )
    This.Copy2Excel_file2_apdist01(  )
    This.Copy2Excel_file4_salesytd(  )
    This.Copy2Excel_file5_invytd(  )
    RELEASE goExcel
RETURN( .T. )
*
FUNCTION Copy2Excel_CreateExcelObject
    #INCLUDE ..\Excel.h
    #INCLUDE ..\wconnect.h
    SET PROCEDURE TO wwUtils   ADDITIVE
    SET CLASSLIB  TO wwIPStuff ADDITIVE
    PUBLIC goExcel
    PUBLIC goSMTP
    goExcel = CREATEOBJECT( "Excel.Application" )
    goExcel.Visible = .F.
    goSMTP  = CREATEOBJECT("wwIPStuff")
RETURN( .T. )
*
FUNCTION Copy2Excel_file1_apchck01
    CPA0010frm.StatusMessage.Caption ="Creating A/P Check Register Excel WorkBook"  
    CLOSE DATABASES
    WITH goExcel
        .SheetsInNewWorkbook             = 1
        .Workbooks.Add 
        .ActiveSheet.Name                = "AP Checks"
        .ActiveWindow.DisplayGridlines   = "FALSE"
        .ActiveWindow.DisplayHeadings    = "FALSE"
        .Application.DisplayFormulaBar   = "FALSE"
        .ActiveSheet.EnableCalculation   = "True"
        .Cells.Font.Name                 = "Arial"
        .Cells.Font.Size                 = 10
        ****************************************
        * Set column widths
        ****************************************
        .Columns(01).ColumnWidth         =  0.50
        .Columns(02).ColumnWidth         =  8.00
        .Columns(03).ColumnWidth         =  9.00
        .Columns(04).ColumnWidth         = 11.00
        .Columns(05).ColumnWidth         = 45.00
        .Columns(06).ColumnWidth         = 10.00
        .Columns(07).ColumnWidth         = 13.00
        .Columns(08).ColumnWidth         = 14.00
        .Columns(09).ColumnWidth         =  0.50
        ****************************************
        * Set row heights for heading lines
        ****************************************
        .Rows(01).RowHeight              = 04
        .Rows(02).RowHeight              = 20
        .Rows(03).RowHeight              = 13.2
        .Rows(04).RowHeight              = 13.2
        .Rows(05).RowHeight              = 04
        ****************************************
        * Build heading lines
        ****************************************
        SET CENTURY OFF
        lcSheetHeading = "Accounts Payable Check Register - For " + DTOC( This.dBeg_apchck01 ) + " - " + DTOC( This.dEnd_apchck01 )
        This.Copy2Excel_Insert_value_to_range_or_cell( "b02:h02", "boxed", "center", "Arial Black", "plain", 11, lcSheetHeading )
        *
        This.Copy2Excel_Insert_value_to_range_or_cell( "b03",     "nobox", "left",   "Arial",       "bold",  10, "Check"     )
        This.Copy2Excel_Insert_value_to_range_or_cell( "c03",     "nobox", "left",   "Arial",       "bold",  10, "Check"     )
        This.Copy2Excel_Insert_value_to_range_or_cell( "d03",     "nobox", "left",   "Arial",       "bold",  10, "Vend."     )
        This.Copy2Excel_Insert_value_to_range_or_cell( "e03",     "nobox", "left",   "Arial",       "bold",  10, "Vendor"    )
        This.Copy2Excel_Insert_value_to_range_or_cell( "f03",     "nobox", "left",   "Arial",       "bold",  10, "Invoice"   )
        This.Copy2Excel_Insert_value_to_range_or_cell( "g03",     "nobox", "left",   "Arial",       "bold",  10, "Reference" )
        This.Copy2Excel_Insert_value_to_range_or_cell( "h03",     "nobox", "right",  "Arial",       "bold",  10, "Check"     )
        *    
        This.Copy2Excel_Insert_value_to_range_or_cell( "b04",     "nobox", "left",   "Arial",       "bold",  10, "Number"    )
        This.Copy2Excel_Insert_value_to_range_or_cell( "c04",     "nobox", "left",   "Arial",       "bold",  10, "Date"      )
        This.Copy2Excel_Insert_value_to_range_or_cell( "d04",     "nobox", "left",   "Arial",       "bold",  10, "Number"    )
        This.Copy2Excel_Insert_value_to_range_or_cell( "e04",     "nobox", "left",   "Arial",       "bold",  10, "Name"      )
        This.Copy2Excel_Insert_value_to_range_or_cell( "f04",     "nobox", "left",   "Arial",       "bold",  10, "Number"    )
        This.Copy2Excel_Insert_value_to_range_or_cell( "g04",     "nobox", "left",   "Arial",       "bold",  10, "Number"    )
        This.Copy2Excel_Insert_value_to_range_or_cell( "h04",     "nobox", "right",  "Arial",       "bold",  10, "Amount"    )
        *
        This.Copy2Excel_Insert_value_to_range_or_cell( "b05:h05", "boxed", "center", "Arial Black", "plain", 10, " " )
    ENDWITH      
    *********************************************
    * Populate the detail lines
    *********************************************
    ln1stDetailRow = 6
    lnRowNum       = 6
    lcFirstColumn  = LTRIM(  STR( ln1stDetailRow, 3, 0 ) )
    lcLastColumn   = LTRIM(  STR( ln1stDetailRow, 3, 0 ) )
    lcRange        = lcFirstColumn + ":" + lcLastColumn
    goExcel.Rows( lcRange ).Select
    goExcel.ActiveWindow.FreezePanes = "TRUE"
    goExcel.Cells(  6,1 ).Select
    This.cFile2Use = "apchck01"
    This.cDestFile = This.cDestPath   + This.cFile2Use
    USE ( This.cDestFile ) IN 0 
    IF RECCOUNT(  ) = 0
        goExcel.Cells( lnRowNum + 2, 2 ).Value = "Nothing to report, no transactions found."
    ELSE
        SET INDEX TO ( This.cDestFile )
        SCAN ALL
            lcInvno = PADR( ALLTRIM( invno ), 8 )
            lcRef   = PADR( ALLTRIM( ref ),  10 )
            WITH goExcel 
                 .Cells( lnRowNum, 2 ).NumberFormat = "@"      
                 .Cells( lnRowNum, 3 ).NumberFormat = "mm/dd/yy;@"      
                 .Cells( lnRowNum, 4 ).NumberFormat = "@"      
                 .Cells( lnRowNum, 5 ).NumberFormat = "@"      
                 .Cells( lnRowNum, 6 ).NumberFormat = "@"      
                 .Cells( lnRowNum, 7 ).NumberFormat = "@"      
                 .Cells( lnRowNum, 8 ).NumberFormat = "###,###.00"      
                 .Cells( lnRowNum, 2 ).HorizontalAlignment = "right"
                 .Cells( lnRowNum, 3 ).HorizontalAlignment = "center"
                 .Cells( lnRowNum, 4 ).HorizontalAlignment = "left"
                 .Cells( lnRowNum, 5 ).HorizontalAlignment = "left"
                 .Cells( lnRowNum, 6 ).HorizontalAlignment = "left"
                 .Cells( lnRowNum, 7 ).HorizontalAlignment = "left"
                 .Cells( lnRowNum, 2 ).Value = checkno
                 .Cells( lnRowNum, 3 ).Value = checkdate
                 .Cells( lnRowNum, 4 ).Value = vendno
                 .Cells( lnRowNum, 5 ).Value = company
                 .Cells( lnRowNum, 6 ).Value = lcInvno
                 .Cells( lnRowNum, 7 ).Value = lcRef
                 .Cells( lnRowNum, 8 ).Value = aprpay
            ENDWITH        
            lnRowNum = lnRowNum + 1
        ENDSCAN
        CPA0010frm.StatusMessage.Caption ="Calculating totals for A/P Check Register"  
        WITH goExcel
             .Cells( lnRowNum, 8 ).NumberFormat = "###,###.00"      
             .Cells( lnRowNum, 7 ).Value = "Total"
             .Cells( lnRowNum, 8 ).Select
             lnFirstDataRow  = ln1stDetailRow
             lnLastDataROw   = lnRowNum       - 1
             lcTotalCell     = "G" + LTRIM( STR( lnRowNum,          4,0 ) )
             lnFirstDataCell = "H" + LTRIM( STR( lnFirstDataRow, 4, 0 ) )
             lnLastDataCell  = "H" + LTRIM( STR( lnLastDataROw,  4, 0 ) )
             lcFormulaCell   = "H" + LTRIM( STR( lnRowNum,          4,0 ) )
             lcFormula       = "=SUM(" + lnFirstDataCell + ":" + lnLastDataCell + ")"
             .ActiveCell.Formula             = lcFormula
             ** .Cells( lnRowNum, 8 ).Calculate
             ** .Application.Calculate
             lcRange                           = lcTotalCell + ":" + lcFormulaCell
             .Range( lcRange ).Font.Bold       = "TRUE"
        ENDWITH
    ENDIF
    goExcel.Range("A1").Select
    lcExcelFileName = "Crown Meat AP Checks WE" + DTOS( DATE(  ) )
    lcExcelSaveName = This.cExcelPath + lcExcelFileName 
    lcDeleteName    = '"' + lcExcelSaveName + '.xls"'
    IF FILE ( lcDeleteName )
        ERASE &lcDeleteName
    ENDIF
    goExcel.ActiveWorkbook.Saveas( lcExcelSaveName, xlExcel7 )
    CLOSE DATABASES 
    CPA0010frm.StatusMessage.Caption ="Completed A/P Check Register Excel WorkBook"  
RETURN( .T. )
*
*
*
FUNCTION Copy2Excel_Insert_value_to_range_or_cell
    LPARAMETERS lc_Range, lc_Boxed, lc_HorizAlign, lc_FontName, lc_Bold, ln_FontSize, lc_CellValue
    LOCAL ln_HorizLign, lc_FontBold
    lc_Bold       = LOWER( lc_Bold       )
    lc_HorizAlign = LOWER( lc_HorizAlign )
    IF EMPTY( lc_FontName )
        lc_FontName = "Arial"
    ENDIF
    IF EMPTY( lc_Boxed )
        lc_Boxed = "nobox"
    ENDIF
    DO CASE
        CASE lc_Bold = "Bold"
             lc_FontBold = "TRUE"
        OTHERWISE
             lc_FontBold = "FALSE"
    ENDCASE
    DO CASE
        CASE lc_HorizAlign = "center"
            ln_HorizLign = xlHAlignCenter
        CASE lc_HorizAlign = "right"
            ln_HorizLign = xlHAlignRight
        CASE lc_HorizAlign = "left"
            ln_HorizLign = xlHAlignLeft
    ENDCASE
    WITH goExcel
        WITH .Range( lc_Range )
            .Merge
            .Font.Name               = lc_FontName
            .Font.Bold               = lc_FontBold
            .Font.Size               = ln_FontSize
            .HorizontalAlignment     = ln_HorizLign
            IF LEFT( LOWER( lc_Boxed ), 3 ) = "box"
                .Interior.Color      = RGB( 210, 210, 210 )
                .Borders.LineStyle   = xlContinuous
                .Borders.Weight      = xlMedium
                .Borders.ColorIndex  = xlColorIndexBlack
            ENDIF
            .Formula                 = lc_CellValue
        ENDWITH
    ENDWITH
RETURN( .T. )