EXCEL

[sunningdale]

How does one set the Font to selected cells

OJPGLIST:Cells("A1:J1"):Font:Bold := .T.

does not work so obviously I have done something wrong.

Also how does one set Save as Type.

Save as keeps the same file type. Can one select another?

[mol]

It really doesn't work in Excel 2007, but now, I'm testing with Office 2010 and works! Strange...

[dhaine_adp]

Hi,

OJPGLIST:Cells("A1:J1"):Font:Bold := .T.

does not work so obviously I have done something wrong.

Try it with:

Code: Select all

LOCAL cRange := "A1:J1"

oSelection := oSheet:Range(cRange)
oSelection:Font:Bold := .t.

or

oSheet:Cells(1, 1):Font:Bold := .t.  // you can also use for next loop using this syntax

For more of these Excel commands, record macro from and excel and then convert the resulting VBA code to Harbour Code. Check MSDN to see the class memberships, methods and properties for each Class.

Regards,

Danny

[pctoledo]

another syntax:

Code: Select all

OJPGLIST:Range("A1:J1"):Font:Bold := .T.

[Rathinagiri]

Thanks a lot Toledo.

[pctoledo]

Friends, other commands:

Code: Select all

oExcel := TOleAuto():New( "Excel.Application" )
oExcel:WorkBooks:Add()
oSheet := oExcel:Get( "ActiveSheet" )

/* Font name */
oSheet:Cells:Font:Name := "Times New Roman"
oSheet:Cells:Font:Size := 10

/* Rename Sheet Tab */
oSheet:Name := "Sheet Test"

/* Value Cell */
oSheet:Cells( 1, 1 ):Value := "Name"
oSheet:Cells( 1, 2 ):Value := "Address"

/* Add Sheet Tab */
oExcel:Sheets:Add()
oSheet := oExcel:Get( "ActiveSheet" )
oSheet:Name := "Sheet Tab 2"

/* Column width */
oSheet:Columns(1):ColumnWidth := 3.86
oSheet:Columns(2):ColumnWidth := 40

/* Formatting font */
oSheet:Cells( 1, 1 ):Font:Size := 14
oSheet:Cells( 1, 1 ):Font:Bold := .T.
oSheet:Cells( 2, 1 ):Font:Size := 10

/* Alignment */
oSheet:Cells( 1, 1 ):HorizontalAlignment:= -4108 //Center
oSheet:Cells( 1, 2 ):HorizontalAlignment:= -4131 //Left
oSheet:Cells( 1, 3 ):HorizontalAlignment:= -4152 //Right
oSheet:Range("A2:M2"):HorizontalAlignment:= -4131 //Left A2:M2

/* Merge Cells */
oSheet:Range("A4:M4"):Merge()

/* Set Cell format */
oSheet:Cells( 6, 2 ):Set( "NumberFormat", "#.##0,00" )

/* Color font */
oSheet:Cells( 1, 1 ):Font:ColorIndex := 3

/* Color fill (background) */
oSheet:Cells( 1, 2 ):Interior:ColorIndex := 7

/* Borders */
oSheet:Range("A2:M10"):Borders(1):LineStyle:= 1
oSheet:Range("A2:M10"):Borders(2):LineStyle:= 1
oSheet:Range("A2:M10"):Borders(3):LineStyle:= 1
oSheet:Range("A2:M10"):Borders(4):LineStyle:= 1

/* Locking cells */
oSheet:Columns("G:G"):Select()
oSheet:Columns("G:G"):Locked := .T.
/* Hidden formula */
oSheet:Columns("G:G"):FormulaHidden := .T.

/* Replace text */
oSheet:Cells:Replace("search", "replace")

/* Password protect */
oSheet:Protect("my_password")

/* Save as */
oExcel:ActiveWorkbook:SaveAs( "c:\path\name.xls" )

oSheet:Cells( 1, 1 ):Select()
oExcel:Visible := .T.

[esgici]

Friends, other commands:
...

Gracias amigo;

really useful and valuable tips

Viva HMG

[sunningdale]

I would not dare to suggest that you chaps do not already know this but to change file type see my prg file and line 983 in fileformat.txt

Someone out there might find it of help.

[pctoledo]

completing...

Basic Color for Font:ColorIndex and. Interior:ColorIndex

Black = 1
White = 2
Blue = 5
Light Blue = 41
Dark Blue = 11
Green = 10
Red = 3
Yellow = 6
Marron = 53
Violet = 13
Orange = 46
Pink = 7

Open file XLS:

Code: Select all

oExcel:WorkBooks:Open( "c:\path\namefile.xls" )

[dhaine_adp]

Hi,

Here is some more, code snippet of building Monte Carlo Simulation in MS Excel from HMG/HB:

Code: Select all

   DBSELECTAREA( "FORECAST" )
   DBGOTOP()
   cTxtStream := ""
   WHILE .NOT. EOF()
      **- Monte Carlo Parameters and Data
      nStep := VAL( FIELDGET( 1 ) ) + 1
      cCurXlCol := aMonteCols_[ nStep-1 ]
      nxlRow := 4
      FOR ii := 1 TO LEN( aMonteLbl_ ) - 1
         DO CASE
            CASE ii = 1
               oSheet:Cells( nxlRow++, nStep ):Value := FIELDGET( 2 )
            CASE ii = 2
               oSheet:Cells( nxlRow++, nStep ):Value := FIELDGET( 3 )
            CASE ii = 3
               **Estimate Upper Bound := =STDEVP(B4:B5,AVERAGE(B4:B5))
               cXLRange := cCurXlCol + "4:" + cCurXlCol + "5"
               cFormula := "=STDEVP(" + cXLRange + ",AVERAGE(" + cXlRange + "))"
               oSheet:Cells( nxlRow++, nStep ):Value := cFormula
            CASE ii = 4
               **Absolute Error at 2% := =AVERAGE(B4:B5)/50
               cXLRange := cCurXlCol + "4:" + cCurXlCol + "5"
               cFormula := "=AVERAGE(" + cXLRange + ")/50"
               oSheet:Cells( nxlRow++, nStep ):Value := cFormula
            CASE ii = 5
               **Iterations := =((3*B6)/B7)^2
               cFormula := "=((3*" + cCurXLCol + "6)/" + cCurXLCol + "7)^2"
               oSheet:Cells( nxlRow, nStep ):Value := cFormula
               nIterate := INT( oSheet:Cells( nxlRow, nStep ):Value )
               IF nIterate > 1048576
                  nIterate := 1048576  // Microsoft Excel 2007 Maximum Rows
               ENDIF
               nxlRow++
            CASE ii = 6
               ** Standard Deviation from Population := =STDEVP(B12:B12150)
               cXLRange := cCurXlCol + "12:" + cCurXlCol + HB_NTOS( nIterate )
               cFormula := "=STDEVP(" + cXLRange + ")"
               nIterate := oSheet:Cells( nxlRow++, nStep ):Value := cFormula
            CASE ii = 7
               ** Distribution Skewness := =SKEW(B12:B12150)
               cFormula := "=SKEW(" + cXLRange + ")"
               nIterate := oSheet:Cells( nxlRow++, nStep ):Value := cFormula
            CASE ii = 8
               ** Kurt (Kurtosis) := =KURT(B12:B12150)
               cFormula := "=KURT(" + cXLRange + ")"
               nIterate := oSheet:Cells( nxlRow++, nStep ):Value := cFormula
         ENDCASE
      NEXT
      DBSKIP()
   END

   **Monte Carlo := =RAND()*($B$5-$B$4)+$B$4
   FOR ii := 1 TO 42
      cCurXLCol := aMonteCols_[ ii ]
      nIterate := oSheet:Cells( 8, ii+1 ):Value
      nxlRow := 12
      FOR nxlRow := 12 TO 12 + INT( nIterate )
         cFormula := "=RAND()*($" + cCurXLCol + "$5-$" + cCurXLCol + "$4)+$" + cCurXLCol + "$4"
         oSheet:Cells( nXlRow, ii+1 ):Value := cFormula
         **MSGINFO( HB_NTOS( 12 + nIterate - 12 ) + " / " + HB_NTOS( 12 + nIterate ),  )
      NEXT
      EXIT
   NEXT


   oExcel:Sheets:Add()
   oSheet := oExcel:ActiveSheet()
   oSheet:Name := "RA"
   oSheet:Columns( "A:F" ):Select()
   oSheet:Columns( "A:F" ):ColumnWidth := 11
   oSheet:Columns( "A:F" ):HorizontalAlignment := xlCenter
   oSheet:Columns( "A:F" ):VerticalAlignment := xlCenter
   oSheet:Range( "A1" ):Activate()
   oSheet:Range( "A1" ):Value := cA1Data
   oSheet:Range( "A2" ):Activate()
   oSheet:Range( "A2" ):Value := cA2Data
   oSheet:Cells( 1, 1 ):HorizontalAlignment := xlLeft
   oSheet:Cells( 2, 1 ):HorizontalAlignment := xlLeft
   oSheet:Range( "A3" ):Select()
   oSheet:Range( "A3" ):Activate()
   oSheet:Paste()
   oSheet:Range( "A3" ):Activate()
   oSheet:Range( "A3:F3" ):Select()
   oSheet:Range( "A3:F3" ):AutoFilter()
   oSheet:Range( "B4" ):Activate()
   oExcel:ActiveWindow:FreezePanes := .t.
   oSheet:Range( "A4" ):Activate()

   **auto filter
   oSheet:Range( "A3:U3"):Select()
   oSheet:Range( "A3:U3" ):AutoFilter()