Date:  08/07/2014 09:01:53 AM Msg ID:  004711
From:  Ali Koumaiha Thread:  004711
Subject:  Excel automation from VFP and from FWX
 I have a script that users can click and generate an excel file to download.
 
in VFP (from same machine) i can run the function and works well.
 
in the FWX, I am getting an error Unknown com Status on the saving part only.
 
the error is happening on the SAVEAS toward the end of the code.
I even tried, oExcel.AciveWorkbook.SaveAs(lcFileName)
I get OLE idDisplatch  unable to get the SaveAs property of the workbook class
 
Again, if I run this code in VFP on the same server, it works...
 

 
<%
FUNCTION ExportToExcel(tcFileName)
    Local lcFileName
    SELECT CurBilling
    LOCATE
    IF EOF()
        return
    ENDIF
    #DEFINE xlsum -4157

    lcRetFileName = ''
    lcFileName = 'c:\inetpub\wwwroot\excel\'+alltrim(tcFileName) + '.xlsx'
    if file(lcFileName)
        erase (lcFileName)
    endif
   
    try
        *!* Create a reference to an Excel OLE object
        oExcel = CREATEOBJECT("Excel.application") 

        With oExcel   
            *!* Add a new workbook
            .application.workbooks.Add

            *!* Make Excel visible
                .Visible = .f.
                .DisplayAlerts = .f.
        *!* Add records to workbook
            .Range("A1").Value = "Serial Number"
            .Range("B1").Value = "Model"
            .Range("C1").Value = "Model Description"
            .Range("D1").Value = "Warranty?"       
            .Range("E1").Value = "Work Order#"       
            .Range("F1").Value = "Item Number"                       
            .Range("G1").Value = "Item Description"       
            .Range("H1").Value = "Qty"       
            .Range("I1").Value = "ASC Price"       
            .Range("J1").Value = "Price"               
            .Range("K1").Value = "Total"               
            y = 2
            SCAN
                .Range("A"+TRANSFORM(y)).Value = IIF(ISDIGIT(Serial),['],'')+ Serial
                .Range("B"+TRANSFORM(y)).Value = Model
                .Range("C"+TRANSFORM(y)).Value = ModelDesc
                .Range("D"+TRANSFORM(y)).Value = Warranty
                .Range("E"+TRANSFORM(y)).Value = WONum
                .Range("F"+TRANSFORM(y)).Value = IIF(ISDIGIT(Item),['],'')+Item
                .Range("G"+TRANSFORM(y)).Value = ItmDesc
                .Range("H"+TRANSFORM(y)).Value = Qty
                .Range("I"+TRANSFORM(y)).Value = NTOM(asc)
                .Range("J"+TRANSFORM(y)).Value = NTOM(price)
                .Range("K"+TRANSFORM(y)).Value = NTOM(subtotal)
                Y = Y + 1
            ENDSCAN
            .Range("A1:K"+TRANSFORM(Y)).Select
            .ActiveSheet.UsedRange.EntireColumn.Autofit       
            oSelected = .Selection
        ENDWITH 
        COMARRAY(oSelected, 11)
        LOCAL laArray(1)
        laArray(1) = 11
        oSelected.Subtotal(1, xlsum, @laArray, .T., .F., .T.)     
        oExcel.ActiveSheet.UsedRange.EntireColumn.Autofit
        oSheet = oExcel.ActiveSheet
        oSheet.SaveAs(lcFileName)
        oExcel.Quit
        release oExcel
        lcRetFileName = lcFileName
       
    catch to loExp
        lcRetFileName = ''
        oPortal.IEMessagebox('Error creating detailed excel file' + chr(13) +  loExp.Message)
        if type('oExcel') = 'O'
            oExcel.Quit
            release oExcel
        endif
       

    endtry
    return lcRetFileName
   
ENDFUNC

%>