filtering output to Excel

[Ehab Samir Aziz]

I am trying to filter DBF to get variable data into excel sheet . I built my filter fine but I got Harbour Exception error .

Code: Select all  Expand view

function executexcel(oCombo1,oCombo2,oCombo3,oCombo4,oCombo5,oCombo6,oCombo7,oCombo8,oCombo9,oCombo10)
*--------------------------------------------------------------------------------------------------------

LOCAL cPath := "E:\programs\clipper\fwh\sitex\test.XLS"
local oExcel , oBook, oSheet
LOCAL nline:=5
local ncol
local nCounter:=1
local nStart := nCounter
local cMemo := ''
local a2_fields[12]
local a2_vars[12]
local cFilter:=""
local cValue:=""
local i:=0

select 4
use mete
index on mete->ME_mc_serl to mete
index on str(mete->year) + str(mete->month) to mete1
index on mete->me_date to mete2

a2_fields[1]:="(4)->Me_CU_ACCt"
a2_fields[2]:="(4)->Me_CU_name"
a2_fields[3]:="(4)->Me_CT_CTNA"
a2_fields[4]:="(4)->Me_mc_type"
a2_fields[5]:="(4)->Me_mc_modl"
a2_fields[6]:="(4)->Me_mc_serl"
a2_fields[7]:="(4)->Me_mc_brn"
a2_fields[8]:="(4)->Me_mc_mere"
a2_fields[9]:="(4)->Me_mc_dep"
a2_fields[10]:="(4)->Me_mc_loc"
a2_fields[11]:="(4)->Me_date"
a2_fields[12]:="(4)->Me_date"





a2_vars[1]:=oCombo1
a2_vars[2]:=oCombo2
a2_vars[3]:=oCombo3
a2_vars[4]:=oCombo4
a2_vars[5]:=oCombo5
a2_vars[6]:=oCombo6
a2_vars[7]:=oCombo7
a2_vars[8]:=oCombo8
a2_vars[9]:=oCombo9
a2_vars[10]:=oCombo10
a2_vars[11]:=ctod("01/06/2006")
a2_vars[12]:=ctod("30/06/2006")

? oCombo1
? oCombo2
? oCombo3

For i := 1 to 12

     IF Empty(a2_Vars[i])     && Don't process empty variables
         LOOP
     ENDIF

     DO CASE            && Create char value
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==11
                 cValue:= 'DTOS(' + a2_Fields[i] + ') >= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==12
                 cValue := 'DTOS(' + a2_Fields[i] + ') <= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "N"
             cValue := 'STR(' + a2_Fields[i] + ') == "' + STR(a2_vars[i]) + '"'       && or
         CASE VALTYPE(a2_vars[i]) == "C"
             cValue :="alltrim(upper("+ a2_Fields[i]+"))" + '=="' + alltrim(upper(a2_Vars[i])) + '"'
     ENDCASE
     IF Empty(cFilter)     && this is the first element
         cFilter :=  cValue
     ELSE
         cFilter += " .AND. " + cValue
     ENDIF
NEXT

    ? (cFilter)


OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 1 ), "Value", "branch" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 2 ), "Value", "Type" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 3 ), "Value", "Model" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 4 ), "Value", "Serial" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 5 ), "Value", "Opening" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 6 ), "Value", "closing" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 7 ), "Value", "prints" )
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 8 ), "Value", "waste" )


   select 4
   4->(dbgotop())
   set filter to &(cFilter)
   4->(dbgotop())

DO WHILE !(4)->(EOF())
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 1 ), "Value", (4)->me_mc_brn)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 2 ), "Value", (4)->me_mc_type)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 3 ), "Value", (4)->me_mc_modl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 4 ), "Value", (4)->me_mc_serl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 5 ), "Value", (4)->open_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 6 ), "Value", (4)->cls_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 7 ), "Value", (4)->prints)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 8 ), "Value", (4)->waste)
nLine:=nLine+1
(4)->(DBSKIP(1))
ENDDO



   OleSetProperty( oExcel, "Visible", .t. )




    OLEInvoke(oExcel,"Quit")

RETURN NIL