FiveTech Software tech support forums

Posted: **Tue Jul 25, 2006 5:42 am**

How can I write fixed headers to fields written to Excel sheet using OLE.

FUNCTION buildexcel()
*-------------------

LOCAL cPath := "E:\programs\clipper\fwh\sitex\test.XLS"
local oExcel , oBook, oSheet 
LOCAL nline:=1



   oExcel = CREATEOLEOBJECT( "Excel.Application" ) 

   oBook = OleInvoke( OleGetProperty( oExcel, "WorkBooks" ), "Add" ) 
   oSheet = OleGetProperty( oBook, "WorkSheets", 1 ) 

select 3
use mach index mach3
set filter to alltrim(3->mc_cu_acct)=="2125447"

3->(DBGOTOP()) 
DO WHILE !(3)->(EOF())
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 1 ), "Value", (3)->mc_type )
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 2 ), "Value", (3)->mc_model )
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 3 ), "Value", (3)->mc_serial )
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 4 ), "Value", (3)->mc_brn ) 
nLine:=nLine+1 
(3)->(DBSKIP(1)) 
ENDDO 
   OleSetProperty( oExcel, "Visible", .t. ) 
    OLEInvoke(oExcel,"Quit")
RETURN NIL

Posted: **Tue Jul 25, 2006 5:57 pm**

Ehab Samir Aziz wrote: select 3
use mach index mach3
set filter to alltrim(3->mc_cu_acct)=="2125447"

First, I advise you, to open files without "area number" and afterward use only its alias:

use mach index mach3 NEW

Sele mach3

Ehab Samir Aziz wrote: How can I write fixed headers to fields written to Excel sheet using OLE

a) One way:

FOR nCol := 1 TO FCOUNT()
oSheet:Cells( nRow, nCol ):Value := FieldName( nCol )
NEXT

WHILE !EOF()
nLine++
FOR nCol := 1 TO FCOUNT()
oSheet:Cells( nLine, nCol ):Value := FieldGet( nCol )
NEXT
DBSKIP()
ENDDO

b) Another way:

oSheet:Cells( 3, 1 ):Value := "TYPE"
oSheet:Cells( 3, 2 ):Value := "MODEL"
oSheet:Cells( 3, 3 ):Value := "SERIAL"
oSheet:Range( "A3"):HorizontalAlignment:=7 // centered title

//
// set font and colors
FOR nCol := 1 TO FCOUNT()
oSheet:Cells( 3, nCol ):Font:Size := 10
oSheet:Cells( 3, nCol ):Font:Bold := .t.
oSheet:Cells( 3, nCol ):Font:Color:= 8388608
NEXT

Posted: **Tue Jul 25, 2006 9:36 pm**

with your another way I got error : no exported method cells.

but if I added those lines before the loop it goes well.

Code: Select all



OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 1 ), "Value", "Type" ) 
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 2 ), "Value", "Model" ) 
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 3 ), "Value", "Serial" ) 
OleSetProperty( OleGetProperty( oSheet, "Cells", 3, 4 ), "Value", "Branch" )

Posted: **Tue Jul 25, 2006 9:40 pm**

If you are adding a lot of records I like to use the clipboard and paste the information into Excel. You would be suprised at how much faster it is than updating every cell. More than 100 times faster.

Here is a little sample of how I do it using your fields.

Code: Select all

// nStart is paste counter
nStart := nCounter
cMemo := ''
do while .not. mach->( eof() )
   // use tab between fields for delimiter
   // all variables have to be character type
   // if numeric or date set excel column type beforehand
   cMemo += mach->mc_type          // No tab at the beginning of line
   cMemo += chr(9)+mach->mc_model
   cMemo += chr(9)+mach->mc_serial
   cMemo += chr(9)+mach->mc_brn
   cMemo += CRLF                   // end each line with crlf
   nCounter++
   skip
   // paste every 100 records or eof() ( no more records )
  // I do this to limit memory required for memo var
   if mod( nCounter, 100 ) = 0 .or. mach->( eof() )
      oClp     := TClipBoard():New()
      if oClp:Open()
         oClp:SetText( cMemo )
         oClp:Close()
      endif
      // select cell for paste 
      oSheet:Cells( nStart, 1 ):Select()
      oSheet:paste()
      // reset paste counter
      nStart := nCounter
      cMemo := ''
   endif
enddo
// clear clipboard buffer
oClp     := TClipBoard():New()
if oClp:Open()
   oClp:SetText( '' )
   oClp:Close()
endif

Posted: **Wed Jul 26, 2006 8:02 pm**

Application
===========
Path and name: E:\programs\clipper\FWH\sitex\sitex.exe (32 bits)
Size: 1,465,344 bytes
Time from start: 0 hours 0 mins 9 secs
Error occurred at: 26/07/2006, 23:01:49
Error description: Error BASE/1004 No exported method: CELLS

Any good links to OLE issues. I need those tutorials about opening Excel sheets from inside a FWH application .

FiveTech Software tech support forums

writing fixed headers to Excel sheet

writing fixed headers to Excel sheet

Re: writing fixed headers to Excel sheet