Working with Excel

[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

[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  Expand view  RUN

// 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

[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.

[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

[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.



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

Code: Select all  Expand view  RUN

#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

[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.

[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]

Hello Tim,

here you can download the vba language reference of excel 2003

http://www.microsoft.com/downloads/details.aspx?familyid=2204a62e-4877-4563-8e83-4848dda796e4&displaylang=en

[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]

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.

[hua]

Tim,
Here's a write-up available on Ole & Excel, http://wiki.fivetechsoft.com/doku.php?id=ole_excel

[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  Expand view  RUN

..
..
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
...
...
 

Best Regards
Uwe

[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

[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.



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

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



No Gridlines

Code: Select all  Expand view  RUN

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

[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" )