Working with Excel

User avatar
TimStone
Posts: 2536
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA
Contact:

Working with Excel

Post by TimStone »

Where can I find a complete description of commands available when working with Excel ? I see examples here of certain tasks, and I know more can be done, but it would help to find the documentation for all the commands available to us.

The same would apply to Outlook and Word.

Thanks for pointing me in the right direction.

Tim
Tim Stone
http://www.MasterLinkSoftware.com
timstone@masterlinksoftware.com
Using: FWH 19.06 with Harbour 3.2.0 / Microsoft Visual Studio Community 2019
User avatar
ukoenig
Posts: 3981
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: Working with Excel

Post by ukoenig »

Hello Tim,

You have to know more about VBA ( Visual-Basic for Application, NOT EASY ! )
I created for a Customer of mine many Exel-Sheets from inside the FWH-Application

to show how it works and Prg-Structure, a Example
( In case You need english Informations, I can translate and explain in english )

A sample : creating a Statistic Export-Form from a DBF :

Code: Select all

// FARBTABELLE
//-------------------------

// Low Colors
//----------------------------
// Schwarz      RGB(   0,   0,   0 )
// Blau             RGB(   0,   0, 128 )
// Grün            RGB(   0, 128,   0 )
// Cyan            RGB(   0, 128, 128 )
// Rot              RGB( 128,   0,   0 )
// Magenta       RGB( 128,   0, 128 )
// Braun          RGB( 128, 128,   0 )
// Grau            RGB( 192, 192, 192 )

// Light Colors
//-------------------------
// Grau            RGB( 128, 128, 128 )
// Blau             RGB(   0,   0, 255 )
// Grün             RGB(   0, 255,   0 )
// Cyan            RGB(   0, 255, 255 )
// Rot               RGB( 255,   0,   0 )
// Magenta       RGB( 255,   0, 255 )
// Gelb             RGB( 255, 255,   0 )
// Weiß            RGB( 255, 255, 255 )

// Format
//---------------
//   '0'
//   '0.00'
//   '#,##0'
//   '#,##0.00'
//   '0%'
//   '0.00%'
//   '0.00E+00'
//   'dd/mm/yy'
//   'dd\-mmm\-yy'
//   'dd\-mmm'
//   'mmm\-yy'
//   'h:mm\ a.m./p.m.'
//   'h:mm:ss\ a.m./p.m.'
//   'h:mm'
//   'h:mm:ss'
//   'dd/mm/yy\ h:mm'
//   'mm:ss'

// xRange := ltrim(str(nRow)) // inside Range work with non defined Lines ( Makro )
// "A&xRange:C&xRange"  Zelle A - C  mit variabler Zeile
// oSheet:Range( "C1:D1" ):Merge()  // connect Cells
// oSheet:Cells( 1, 3 ):Set("WrapText",.T.) // writes more than 1 Row to a Cell
// oSheet:Range( "C1:D2" ):Set("WrapText",.T.) // writes more than 1 Line to Cell-range
// oSheet:Range( "C1:D1" ):Interior:Color := RGB( 255, 255, 255 )  // Color of Cell-range
// oSheet:Range( "C1:D1" ):Font:Color := RGB( 255, 255, 255 ) // Font-Color for Cell-range
// oSheet:Cells( 1, 3 ):Borders():LineStyle := 1  // Cell-border  Type 1, 2, 3 oder 4
// oSheet:Cells( 1, 3 ):Font:UnderLine := lUnderLine  // Underline
// oSheet:Cells( 1, 3 ):AddComment("Hallo") // add Comment to a Cell
// oSheet:Columns( nCol ):AutoFit() // Automatic Col-adjust
// oSheet:PrintOut() // print Sheet
// oSheet:Columns( n ):AutoFit() // Autom. Col-adjust of total column 'n'
// nSheets := oExcel:Sheets:Count() // count open Sheets
// opens defined Sheet
// oExcel:WorkBooks:Open( cFilePath( GetModuleFileName( GetInstance() ) ) + test.xls") 
// nRows := oSheet:UsedRange:Rows:Count() // counts used Rows
// nCols := oSheet:UsedRange:Columns:Count() // counts used Col's
// oExcel:Set( "DisplayAlerts", .t. ) // show EXCEL-messages  .t. = Yes,   .f. =  No
// oWin:Set( "FreezePanes", .t. ) // Freeze Headlines

// -------------------------------------------------

STATIC FUNCTION Data1Excel(oMeter,nTOTAL,oText, lEnd,oClp)
LOCAL oSelection, oWin, oExcel, oWorkBook, oSheet
LOCAL cRange     := ""

// MUSTER
// LOCAL aFormat  := { { "Pos.", 3.2,  "@", xlLeft },;
// { "Artikel", 21.3,  "@", xlLeft   },;
// { "Datum", 9, "DD/MM/YYYY", xlCenter },;
// { "Einheitspreis", 8.2,  "#.##0,00", xlRight  },;
// { "Menge", 9, "#.##0,00", xlRight  },;
// { "Gesamt Preis", 9.5, "#.##0,0", xlRight  }  }  


//                             Celltext               Size        Format              Orientation
// -------------------------------------------------------------------------------------

LOCAL aFormat := { { "Datum", 10, "TT.MM.JJJJ", xlRight },;
     { "Konto", 8, "@", xlRight },;
     { "Gegen", 8, "@", xlRight },;
     { "Journal", 8, "@", xlRight },;
     { "Beleg", 8, "@", xlRight },;
     { "Soll", 12.2, "#.##0,00", xlRight },;    
     { "Haben", 12.2, "#.##0,00", xlRight },;    
     { "Steuer", 6, "@", xlRight },; 
     { "Buchungstext", 25, "@", xlLeft } }

LOCAL nSheets    := 0
LOCAL n          := 0
LOCAL nRow       := 0
LOCAL nHeaderRow := 1 // Row-No. of Cell-Headline
LOCAL nDataStart := nHeaderRow + 1 // Row-No. for Top of Table

oExcel    := TOleAuto():New( "Excel.Application" )
oWorkBook := oExcel:WorkBooks:Add()
nSheets := oExcel:Sheets:Count()
for n := 1 to nSheets - 1
     oExcel:Worksheets( n ):Delete()
next
oExcel:Set( "DisplayAlerts", .f. )
oSheet := oExcel:Get( "ActiveSheet" )
oWin := oExcel:Get( "ActiveWindow" )
oSheet:Name := aktNUMMER
oSheet:Cells( nDataStart, 1 ):Select()
oWin:Set( "FreezePanes", .t. )
oSheet:Cells:Font:Size := 10
oSheet:Cells:Font:Name := "Arial"

for n := 1 to len( aFormat )
      oSheet:Cells( nHeaderRow, n ):Font:Size := 10
      oSheet:Cells( nHeaderRow, n ):Font:Bold := .t.
      oSheet:Cells( nHeaderRow, n ):Value := aFormat[ n ][ F_HEADER ]
      oSheet:Columns( n ):Set( "HorizontalAlignment", aFormat[ n ] [ F_JUSTIFY ] )
      oSheet:Columns( n ):Set( "ColumnWidth",         aFormat[ n ] [ F_LEN ]     )
      oSheet:Columns( n ):Set( "NumberFormat",        aFormat[ n ] [F_FMT ]      )
      oSheet:Cells( nHeaderRow, n ):Interior:Color := RGB(0,0,128)
      oSheet:Cells( nHeaderRow, n ):Font:Color :=    RGB( 255, 255, 255 )
      oSheet:Cells( nHeaderRow, n ):Interior:Pattern := 1
next

cRange := "A" + chr( 48 + nHeaderRow ) + ":" + chr( len( aFormat ) + 64 ) + chr( 48 + nHeaderRow )

oSelection := oSheet:Range( cRange )
oSelection:Borders( 9 ):LineStyle := 1
oSelection:Borders( 9 ):Weight    := 2
oSelection:Borders( 9 ):Set( "ColorIndex", -4105 )

nRow := nHeaderRow
oText:SetText( cDATNAME2 ) 
oMeter:nTotal := nTOTAL
oClp:Clear()
SysRefresh()

nDUMMY1 := "0"
nDUMMY2 := "0.00"

nPos := 1
nRow := nHeaderRow
KOPFAKTIV := .T.
aktNUMMER := (10)->KTONUMMER // DBF-Field


DO WHILE ! eof()

   xRange := ltrim(str(nRow))   

   nRow++

// Sample
//     oClp:SetText( str( nRow - nHeaderRow, 4, 0 ) + "." + chr( 9 ) + ;
//                    wrk->article + chr( 9 ) + ;
//                    transform( wrk->date, "@D" ) + chr( 9 ) + ;
//                    transform( wrk->p_unit, "@E 999,999.99" ) + chr( 9 ) + ;
//                    transform( wrk->qty, "99999" ) + chr( 9 ) + ;
//                    transform( wrk->p_unit * wrk->qty, "@E 999,999.99" ) )

// For calculations You must use TRANSFORM 
//  D  = Date
//  E  = Numbers in europ. 
// Sample : TRANSFORM ( 123456, "999.999" )  =  123,456


  ** write DBF-Fields
  ** -------------------------
  oClp:SetText(   transform( (10)->DATUM, "@D" ) + chr( 9 ) + ;
                                        (10)->KTONUMMER + chr( 9 ) + ;
                                        (10)->GEGENKONTO + chr( 9 ) + ;
                         transform( (10)->JSEITE, "9999" ) + chr( 9 ) + ;
                         transform( (10)->BELEGNR, "9999" ) + chr( 9 ) + ;
                         transform( (10)->SOLL, "@E 999,999.99" ) + chr( 9 ) + ;    
                         transform( (10)->HABEN, "@E 999,999.99" ) + chr( 9 ) + ;    
             transform( (10)->STEUER, "99" ) + chr( 9 ) + ;
                       OemToAnsi((10)->TEXT) )

 oSheet:Cells( nRow, 1 ):Select()
 oSheet:Paste()
 oClp:Clear()

 DBSELECTAREA(10)
 (10)->( DbSkip() )

 nPos++
 oMeter:Set( nPos )
 // oText:SetText( "Konto: " + (10)->KTONUMMER+" "+ OemToAnsi((10)->NAME ))
 oText:SetText( "Konto: " + (10)->KTONUMMER )

 SysRefresh()
 
ENDDO

IF lEnd
 lGesENDE := .T.
ENDIF

oSheet:Cells( 2, 1 ):Select()                // aktiver Cursor
oWorkBook:SaveAs("&cDATNAME1")  //  c_pfad + "\EXCEL\FIBU-KONTEN-"+m->jahr+".XLS"
oClp:Clear()

oExcel:Visible := .T.
// oExcel:Quit()  // if Excel runs in Background -> auskommentieren
oExcel := NIL

RETURN( NIL )
 
Best Regards
Uwe :lol:
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
User avatar
TimStone
Posts: 2536
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA
Contact:

Re: Working with Excel

Post by TimStone »

I follow your example perfectly. I was looking for a command reference for Excel programming ( you suggested VBA ) which would allow me to see the available options and expected parameters.

Thank you.
Tim Stone
http://www.MasterLinkSoftware.com
timstone@masterlinksoftware.com
Using: FWH 19.06 with Harbour 3.2.0 / Microsoft Visual Studio Community 2019
User avatar
ukoenig
Posts: 3981
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: Working with Excel

Post by ukoenig »

Hello Tim,

it was just a quick Information for You.
There are CD's included together with the Books I own, but everything is in German.
I will try, to find something useful in english.

Best Regards
Uwe :lol:
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
User avatar
ukoenig
Posts: 3981
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: Working with Excel

Post by ukoenig »

Hello Tim,

I started with a Excel-project.
Each lesson shows step by step the possibilities, to use
VBA from inside FWH. It starts with a customer-list and
get more complicated from Lesson to the next.
The 1. Lesson is nearly finished and works fine.
I will still add different Fonts, colors and merging Cells of the Sheet-Title
For the Database, Customer.dbf from FWH-samples is used.

Image

Still some Cell-formating has to be done, but it works already to show the Basics.

Code: Select all

#include "Fivewin.ch" 
#include "Image.ch"
#include "Fileio.ch"
#include "Colors.ch"

#define TAB    Chr(9)
#DEFINE xlLeft    -4131 // (0xhffffefdd)
#DEFINE xlRight   -4152 // (0xhffffefc8)
#DEFINE xlCenter   -4108 // (0xhffffeff4)
#DEFINE xlWorkbookNormal -4143 // (0xhffffefd1)

#DEFINE F_HEADER    1
#DEFINE F_LEN       2
#DEFINE F_FMT       3
#DEFINE F_JUSTIFY   4

FUNCTION MAIN() 
LOCAL oDlg, hDC, oClp, oBtn1, oBtn2, oBtn3, oBtn4, oBtn10, aGrad

REQUEST DBFCDX
RddSetDefault("DBFCDX") 
SET _3DLOOK ON

SetBalloon( .T. ) 

DBSELECTAREA(1)
USE Customer ALIAS Test 
ORDCREATE( ,"EXPORT","Last", ;
                      {|| Last } , .F. )

// First C 20
// Last C 20
// Street C 30
// City C 30
// State C 2
// Zip C 10
// Hiredate D 8
// Married L 1
// Age N 2
// Salary N 9.2
// Notes C 70

PRIVATE oButtFont := TFont():New("Arial",0,-16,.F.,.T.,0,0,0,.T. )

DEFINE DIALOG oDlg SIZE 700, 550 TITLE "EXEL => VBA" PIXEL

aGrad := { { 0,50, 3655259, 16777215 }, { 0,50, 16777215, 3655259 } }

DEFINE CLIPBOARD oClp OF oDlg

@ 20, 250 BTNBMP oBtn1 SIZE 80, 30 OF oDlg  2007 ;
FILENAME "Textedit.bmp" ;
LEFT ;
PROMPT "  Lesson &1 " ;
FONT oButtFont ;
ACTION  ( LESSON1(oDlg,oClp) )

oBtn1:lTransparent = .t.   
oBtn1:cTooltip :=  { "Lesson 1" + CRLF + ;
                                  "learning VBA","Excel", 1, CLR_BLACK, 14089979 } 

@ 70, 250 BTNBMP oBtn2 SIZE 80, 30 OF oDlg  2007 ;
FILENAME "Textedit.bmp" ;
LEFT ;
PROMPT "  Lesson &2 " ;
FONT oButtFont ;
ACTION  ( LESSON1(oDlg,oClp) )

oBtn2:lTransparent = .t.   
oBtn2:cTooltip :=  { "Lesson 2" + CRLF + ;
                                  "learning VBA","Excel", 1, CLR_BLACK, 14089979 } 

@ 120, 250 BTNBMP oBtn3 SIZE 80, 30 OF oDlg  2007 ;
FILENAME "Textedit.bmp" ;
LEFT ;
PROMPT "  Lesson &3 " ;
FONT oButtFont ;
ACTION  ( LESSON1() )

oBtn3:lTransparent = .t.   
oBtn3:cTooltip :=  { "Lesson 3" + CRLF + ;
                                  "learning VBA","Excel", 1, CLR_BLACK, 14089979 } 


@ 170, 250 BTNBMP oBtn4 SIZE 80, 30 OF oDlg  2007 ;
FILENAME "Textedit.bmp" ;
LEFT ;
PROMPT "  Lesson &4 " ;
FONT oButtFont ;
ACTION  ( LESSON1() )

oBtn4:lTransparent = .t.   
oBtn4:cTooltip :=  { "Lesson 4" + CRLF + ;
                                  "learning VBA","Excel", 1, CLR_BLACK, 14089979 } 

@ 230, 250 BTNBMP oBtn10 SIZE 80, 30 OF oDlg  2007 ;
FILENAME "Exit.bmp" ;
LEFT ;
PROMPT "  &Exit " ;
FONT oButtFont ;
ACTION  ( oDlg:End() )

oBtn10:lTransparent = .t.   
oBtn10:cTooltip :=  { "Lesson 1" + CRLF + ;
                                  "learning VBA","Excel", 1, CLR_BLACK, 14089979 } 


ACTIVATE DIALOG oDlg CENTER ;
ON PAINT (  GradientFill( hDC, 0, 0, oDlg:nHeight, oDlg:nWidth, aGrad, .T. ), D_ALPHA( hDC ) ) ;
VALID MsgYesNo( "Want to exit ?" )

CLOSE DATABASE

RETURN NIL

// ----------------------------

FUNCTION D_ALPHA( hDC )
LOCAL oBmp1

cALPHA1 :=  "EXCEL.BMP"

DEFINE BITMAP oBmp1 FILENAME "&cALPHA1"
ABPaint( hDC, 5, 5, oBmp1:hBitmap, 220 )
oBmp1:End()

RETURN NIL

// -------------------------

FUNCTION LESSON1(oDlg, oClp)
LOCAL oMeter, oText, lEnd, nTotal

cFile1 :=  "Lesson1"
DBSELECTAREA(1)
nTotal := RECCOUNT()
DBGOTOP()
cCustName := (1)->Last

MsgMeter( { | oMeter, oText, oDlg, lEnd | ;
LESSON1a( oMeter, nTOTAL, oText, @lEnd, oClp ) }, ;
"Name : " + cCustName + CRLF + " " , ;
"create Excel Table..." )

// CLIPBOARD - Clear
//---------------------------
oClp:Clear()

MEMORY( -1 )

RETURN( NIL )

// --------------------------

FUNCTION LESSON1a( oMeter, nTOTAL, oText, lEnd, oClp )
LOCAL oSelection, oWin, oExcel, oWorkBook, oSheet
LOCAL cRange     := ""
LOCAL nSheets    := 0
LOCAL n          := 0
LOCAL nRow       := 0
LOCAL nHeaderRow := 1                            // Line-No. Headline
LOCAL nDataStart := nHeaderRow + 1     // Line-No. Begin of Data

// Define Cell-Format and Size
// ----------------------------------------
LOCAL aFormat  := { { "First",  20,  "@", xlLeft },;
    { "Last",  20,  "@", xlLeft },;
    { "State", 10,  "@", xlLeft },;
    { "Zip",  15,  "@", xlLeft },;
    { "City",  30,  "@", xlLeft },;
    { "Street",  30,  "@", xlLeft },;
    { "Hiredate",  10,  "TT.MM.JJJJ", xlCenter },;
    { "Married",  10,  "@", xlCenter },;
    { "Age",  10,  '0', xlRight },;
    { "Salary",  11.2,  "#.##0,00", xlRight },;
    { "Notes", 50,  "@", xlLeft } }

oExcel    := TOleAuto():New( "Excel.Application" )
oWorkBook := oExcel:WorkBooks:Add()
nSheets := oExcel:Sheets:Count()
FOR n := 1 to nSheets - 1
    oExcel:Worksheets( n ):Delete()
NEXT
oExcel:Set( "DisplayAlerts", .f. )
oSheet := oExcel:Get( "ActiveSheet" )
oWin := oExcel:Get( "ActiveWindow" )
oSheet:Name := "Customer-List"
oSheet:Cells( nDataStart, 1 ):Select()
oWin:Set( "FreezePanes", .t. )
oSheet:Cells:Font:Size := 10
oSheet:Cells:Font:Name := "Arial"

FOR n := 1 to len( aFormat )
    oSheet:Cells( nHeaderRow, n ):Font:Size := 10
    oSheet:Cells( nHeaderRow, n ):Font:Bold := .t.
    oSheet:Cells( nHeaderRow, n ):Value := aFormat[ n ][ F_HEADER ]
    oSheet:Columns( n ):Set( "HorizontalAlignment", aFormat[ n ] [ F_JUSTIFY ] )
    oSheet:Columns( n ):Set( "ColumnWidth",         aFormat[ n ] [ F_LEN ]     )
    oSheet:Columns( n ):Set( "NumberFormat",        aFormat[ n ] [F_FMT ]      )
    oSheet:Cells( nHeaderRow, n ):Interior:Color := RGB(0,0,128)
    oSheet:Cells( nHeaderRow, n ):Font:Color :=    RGB( 255, 255, 255 )
    oSheet:Cells( nHeaderRow, n ):Interior:Pattern := 1
NEXT

cRange := "A" + chr( 48 + nHeaderRow ) + ":" + chr( len( aFormat ) + 64 ) + chr( 48 + nHeaderRow )

oSelection := oSheet:Range( cRange )
oSelection:Borders( 9 ):LineStyle := 1
oSelection:Borders( 9 ):Weight    := 2
oSelection:Borders( 9 ):Set( "ColorIndex", -4105 )

nRow := nHeaderRow
oText:SetText( cCustName )
oMeter:nTotal := nTOTAL
oClp:Clear()
SysRefresh()

nPos := 1
nRow := nHeaderRow
KOPFAKTIV := .T.

DBSELECTAREA(1)
cCustName := (1)->Last

DO WHILE ! eof()
    xRange := ltrim(str(nRow))     // Cell
    nRow++
    oClp:SetText( OemToAnsi( (1)->First ) + chr( 9 ) + ;
                OemToAnsi( (1)->Last ) + chr( 9 ) + ;
                OemToAnsi( (1)->State ) + chr( 9 ) + ;
                OemToAnsi( (1)->Zip )   + chr( 9 ) + ;
                OemToAnsi( (1)->City ) + chr( 9 ) + ;
                OemToAnsi( (1)->Street ) + chr( 9 ) + ;
                transform( (1)->Hiredate, "@D" ) + chr( 9 ) + ;
                                IIF( (1)->Married, "Yes", "No" ) + chr( 9 ) + ;
                                STR((1)->Age ) + chr( 9 ) + ;
                 transform( (1)->Salary,  "@E 999,999.99" ) + chr( 9 ) + ;    
                                 OemToAnsi((1)->Notes) )

    oSheet:Cells( nRow, 1 ):Select()
    oSheet:Paste()
    oClp:Clear()

    DBSELECTAREA(1)
    (1)->( DbSkip() )

    nPos++
    oMeter:Set( nPos )
    oText:SetText( "Konto: " + (1)->Last )

    SysRefresh()
ENDDO

oSheet:Cells( 2, 1 ):Select()    //  Cursor
oWorkBook:SaveAs("Customer-List.xls")  
oClp:Clear()

oExcel:Visible := .T.
// oExcel:Quit()  // if Excel runs at Background
oExcel := NIL

RETURN( NIL )
 
Best Regards
Uwe :lol:
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
User avatar
Massimo Linossi
Posts: 474
Joined: Mon Oct 17, 2005 10:38 am
Location: Italy

Re: Working with Excel

Post by Massimo Linossi »

Hello Ukoenig.
I want to ask you why you are using the clipboard with tabs for putting informations
inside the cells. Is it better or faster than using the settext for each cell ?
Thanks a lot for the info and for the example that you made.
Massimo.
User avatar
anserkk
Posts: 1280
Joined: Fri Jun 13, 2008 11:04 am
Location: Kochi, India

Re: Working with Excel

Post by anserkk »

I want to ask you why you are using the clipboard with tabs for putting informations
inside the cells. Is it better or faster than using the settext for each cell ?
I believe that using clipboard is faster and you don't have to write codes to traverse through the columns. Go to the first column and paste it, rest of the work is done by excel automatically.

Regards
Anser
StefanHaupt
Posts: 824
Joined: Thu Oct 13, 2005 7:39 am
Location: Germany

Re: Working with Excel

Post by StefanHaupt »

Hello Tim,

here you can download the vba language reference of excel 2003

http://www.microsoft.com/downloads/deta ... laylang=en
kind regards
Stefan
User avatar
Massimo Linossi
Posts: 474
Joined: Mon Oct 17, 2005 10:38 am
Location: Italy

Re: Working with Excel

Post by Massimo Linossi »

Hi anserkk.
I've always made this process with positioning to one cell and than placing the text inside, without using the clipboard.
I'm going to test if it is faster as you are saying.
Thanks a lot.
hua
Posts: 861
Joined: Fri Oct 28, 2005 2:27 am

Re: Working with Excel

Post by hua »

Massimo Linossi wrote: I want to ask you why you are using the clipboard with tabs for putting informations
inside the cells.
Massimo.
It's a technique for speed. Used even within xbrowse itself.
FWH 11.08/FWH 19.03
xHarbour 1.2.1 (Rev 6406) + BCC
Harbour 3.1 (Rev 17062) + BCC
Harbour 3.2.0dev (r1904111533) + BCC
hua
Posts: 861
Joined: Fri Oct 28, 2005 2:27 am

Re: Working with Excel

Post by hua »

Tim,
Here's a write-up available on Ole & Excel, http://wiki.fivetechsoft.com/doku.php?id=ole_excel
FWH 11.08/FWH 19.03
xHarbour 1.2.1 (Rev 6406) + BCC
Harbour 3.1 (Rev 17062) + BCC
Harbour 3.2.0dev (r1904111533) + BCC
User avatar
ukoenig
Posts: 3981
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: Working with Excel

Post by ukoenig »

Many changes in Lesson 1 like :

Font- Style and Colors
Cell-formatting
Column reformating
Structure : Headlines, Data and Cursor-Position
A new Appendix added
Notes on each Command-Line
Autosave
Color-Replace in Colums for Condition

Very easy
Inside the Data-loop :
Do we need a SYSREFRESH() ???

Code: Select all

..
..
DO WHILE ! eof()
   xRange := ltrim(str(nRow))     // Cell
   nRow++
   oClp:SetText( OemToAnsi( (1)->First ) + chr( 9 ) + ;
          OemToAnsi( (1)->Last ) + chr( 9 ) + ;
          OemToAnsi( (1)->State ) + chr( 9 ) + ;
          OemToAnsi( (1)->Zip ) + chr( 9 ) + ;
          OemToAnsi( (1)->City ) + chr( 9 ) + ;
          OemToAnsi( (1)->Street ) + chr( 9 ) + ;
          transform( (1)->Hiredate, "@D" ) + chr( 9 ) + ;
          IIF( (1)->Married, "Yes", "No" ) + chr( 9 ) + ;
          STR((1)->Age ) + chr( 9 ) + ;
          transform( (1)->Salary,  "@E 999,999.99" ) + chr( 9 ) + ;    
          OemToAnsi((1)->Notes) )

          oSheet:Cells( nRow, 1 ):Select()
          oSheet:Paste()
          oClp:Clear()

          IF (1)->Age > 50
              // Set ( change ) Cell-Color
              // -----------------------------------
              oSheet:Cells( nRow, 2 ):Interior:Color := RGB( 128, 0, 0 )
              oSheet:Cells( nRow, 2 ):Font:Color := RGB( 255, 255, 255 )
         ENDIF
         IF (1)->Salary < 50000
             // Set ( change ) Cell-Color
             // -----------------------------------
             oSheet:Cells( nRow, 2 ):Interior:Color := RGB( 0, 128, 0 )
             oSheet:Cells( nRow, 2 ):Font:Color := RGB( 255, 255, 255 )
         ENDIF

         (1)->( DbSkip() )

         nPos++
         oMeter:Set( nPos )
         oText:SetText( "Name : " + (1)->Last )

         SysRefresh()
ENDDO
...
...
 
Image

Best Regards
Uwe :lol:
Last edited by ukoenig on Tue Mar 16, 2010 4:03 pm, edited 1 time in total.
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
User avatar
TimStone
Posts: 2536
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA
Contact:

Re: Working with Excel

Post by TimStone »

The people on this forum are amazing. Thank you for all the information. I am putting it all in my OneNote workobook so I have it for continuing reference.

Your input, as always, is greatly appreciated.

Tim
Tim Stone
http://www.MasterLinkSoftware.com
timstone@masterlinksoftware.com
Using: FWH 19.06 with Harbour 3.2.0 / Microsoft Visual Studio Community 2019
User avatar
ukoenig
Posts: 3981
Joined: Wed Dec 19, 2007 6:40 pm
Location: Germany
Contact:

Re: Working with Excel

Post by ukoenig »

I think it is a good idea, to start something like that.

Calculations are very easy.
You don't need to know Excel-Formula
Simply run a Counter ( can have a Condition ) inside the Loop.
At EOF(), You do Your calculation and show the Result at the End of the Sheet.
More funny things are still possible like Screen-Splitting. I will show them in the following Lessons.

I will put everything together as Download in this place, as soon the first Lesson is finished.

Image

Because of all the Settings I can do, a better Solution might be :
A Dialog with 2 Buttons : < Excel Preview > and < Write Source >.
Checkboxes or Radios for all Settings.
Now the User can select his own Combinations and writes the Code for this.

It means : a Excel-Toolbox :roll:

A splitted Sheet horizontal and vertical to 4 Windows
with freezed Title and Headline.

Image

No Gridlines

Image

Code: Select all

nTotalSalary := 0
DO WHILE ! eof()
   xRange := ltrim(str(nRow))     // Cell
   nRow++
   oClp:SetText( OemToAnsi( (1)->First ) + chr( 9 ) + ;
      OemToAnsi( (1)->Last ) + chr( 9 ) + ;
      OemToAnsi( (1)->State ) + chr( 9 ) + ;
      OemToAnsi( (1)->Zip ) + chr( 9 ) + ;
      OemToAnsi( (1)->City ) + chr( 9 ) + ;
      OemToAnsi( (1)->Street ) + chr( 9 ) + ;
      transform( (1)->Hiredate, "@D" ) + chr( 9 ) + ;
      IIF( (1)->Married, "Yes", "No" ) + chr( 9 ) + ;
      STR((1)->Age ) + chr( 9 ) + ;
      transform( (1)->Salary,  "@E 999,999.99" ) + chr( 9 ) + ;    
      OemToAnsi((1)->Notes) )

     oSheet:Cells( nRow, 1 ):Select()
     oSheet:Paste()
     oClp:Clear()

     IF (1)->Age > 50
        // Set ( change ) Cell-Color
        // -----------------------------------
        oSheet:Cells( nRow, 2 ):Interior:Color := RGB( 128, 0, 0 )
        oSheet:Cells( nRow, 2 ):Font:Color := RGB( 255, 255, 255 )
    ENDIF
    IF (1)->Salary < 50000
        // Set ( change ) Cell-Color
        // -----------------------------------
        oSheet:Cells( nRow, 2 ):Interior:Color := RGB( 0, 128, 0 )
        oSheet:Cells( nRow, 2 ):Font:Color := RGB( 255, 255, 255 )
    ENDIF

    nTotalSalary := nTotalSalary + (1)->Salary

    (1)->( DbSkip() )

    nPos++
    oMeter:Set( nPos )
    oText:SetText( "Name : " + (1)->Last )

    SysRefresh()

    IF EOF()
       nRow++
       nRow++
       oSheet:Cells( nRow, 10 ):Font:Size := 12
       oSheet:Cells( nRow, 10 ):Font:Bold := .t.
       oSheet:Cells( nRow, 9 ):Value := "Total :" 

       oSheet:Cells( nRow, 10 ):Interior:Color := RGB(255,255,0)
       oSheet:Cells( nRow, 10 ):Interior:Pattern := 1

       oSheet:Cells( nRow, 10 ):Value := nTotalSalary 
   ENDIF

ENDDO
 
Best Regards
Uwe :lol:
Since 1995 ( the first release of FW 1.9 )
i work with FW.
If you have any questions about special functions, maybe i can help.
Gale FORd
Posts: 663
Joined: Mon Dec 05, 2005 11:22 pm
Location: Houston
Contact:

Re: Working with Excel

Post by Gale FORd »

I learn a lot by recording macros and looking at the code.

1. Start recording macro
2. Do the steps in Excel that you want to perform.
3. Stop recording
4. Look at the code to see what Excel did to accomplish the task

Example recorded macro in "Module1" in the "Modules" folder.
Sub Macro1()
'
' Macro3 Macro
'
Range("C13:E20").Select
Selection.NumberFormat = "$#,##0"
End Sub

The xHarbour code
oSelect := oExcel:ActiveSheet:Range( "C13:E20" ):select()
oSelect:Set( "NumberFormat", "$#,##0" )

or you can shorten it to
oExcel:ActiveSheet:Range( "C13:E20" ):Set( "NumberFormat", "$#,##0" )
Post Reply