HMGforum.com

Code: Select all

#include <hmg.ch>
#include "excel.ch"

Function Main
local cb1,b1

DEFINE WINDOW Main ;
   AT 90,90;
   WIDTH 300;
	 HEIGHT 200;
	 TITLE "Test Excel OLE";
   MAIN
  
   @ 10, 10 BUTTON b1 CAPTION "Ole Excel" ACTION ExcelSum()

END WINDOW

 Main.Center
 Main.Activate

Return
//---------------

function ExcelSum()
   LOCAL cExePath := HB_FNameDir(ExeName())
   local oExcel, oSheet
   local row := 1
		
   oExcel:=CreateObject("Excel.Application")
	 
	 // Alternative: Open Existing Excelsheet from default Excel save location
	 // oExcel:WorkBooks:Open("Demo") 
	 
   if Ole2TxtError() != "S_OK"
      MsgStop("Excel is not available - Error "+Ole2TxtError(), "Warning")
      return nil
   endif
	 	
   oExcel:WorkBooks:Add()
   oSheet:=oExcel:ActiveSheet()
    // MsgBox(oExcel:Activesheet:Name)    // Show current worksheet name
   oSheet:Name:="Test"                         // Give worksheet a name
   oSheet:Cells(row,1):Value := "Just a sum :"
   row := row + 1
   frow := row
   for i := 1 to 15
         oSheet:Cells(row,1):Value := i
         oSheet:Cells(row,3):NumberFormat:="##########0.00"
         row := row + 1
   next i
	 oSheet:Cells(row,1):Formula:="=sum(A"+alltrim(str(frow,5,0))+":A"+alltrim(str(row-1,5,0))+")"
	 oSheet:Cells(row,1):NumberFormat:="##########0.00"
         oSheet:Cells(row,1 ):Font:Bold := .T.        	   // Make a Cell bold
	 oSheet:Rows(1):Font:Bold:=.T.                        // Make first row Bold
	 oSheet:Cells(row,1):Font:Color:=RGB(255,0,0)    // Set last row to color red
	 
	 // Border functions
	 
	 oSheet:Cells(row,1):Borders:Color:=RGB(0,0,255)       // Blue border around a cell
	 oSheet:Cells(row,1):Borders:Weight:=xlThick             // Border thickness
	 
	 oSheet:Range("A10"):Borders(xlEdgeBottom):LineStyle:=xlDouble     // Make Bottom Border double
	 oSheet:Range("A10"):Borders(xlEdgeBottom):Color = RGB(0,255, 0)  // Change bottom border color to green
	 
	 oSheet:Range("A4:A6"):BorderAround(xlContinuous,xlMedium,,1)      // Draw border around range of cells
	 // Syntax: BorderAround(LineStyle, Weight, ColorIndex, Color)
	 // You must specify either ColorIndex or Color, but not both.
	 
	 // Color & Pattern functions
	 
	 oSheet:Range("A1"):Interior:Pattern:=xlPatternLightDown	// add a Pattern to a cell
	 oSheet:Range("A3"):Interior:ColorIndex:=6    			// Colors the cell yellow
	 
	 // Cell comment
	 
	 oSheet:Range("A3"):AddComment("This cell is yellow")       // Add comment to a cell
	 oSheet:Columns( 1 ):AutoFit()                                      // Does an autofit of a column. 
	
        // Select another sheet
	 
	 oExcel:WorkSheets("Sheet2"):Cells(1,1):Value:="Text in sheet 2"   // Write something directly in Sheet 2
	 // or alternatively, you could select sheet 2, then activate it & write in it.
	 // oExcel:Worksheets("Sheet2"):Activate() 				  // Activate second sheet 
	 // oSheet:=oExcel:ActiveSheet()
	 // oSheet:Cells(1,1):Value:="Something"
	
	 oSheet:Range("B1"):Select()   
         oExcel:Visible := .T.
	 
	 //-> Save the sheet in the indicated location & stays open
	 // oSheet:SaveAs(cExePath+"Demo")  // if executed more than once and you say 'no' to overwrite,
	                                                    //  will raise error @ next command
	 //-> Show the name of the saved workbook 
	 // MsgBox(oExcel:ActiveWorkbook:Name)
	 //->Propose to Close a workbook (it will ask for confirmation)
	 // If you put 2 previous commands after the other and execute for the first time, Exel will be closed.
	 // oExcel:Workbooks:Close()
	
   
return nil

/* 
When experimenting with Excel-functions one can encounter 2 errors:
"no exported value": combination x:y:z  not possible
"argument error":  command is correct, but form of argument is incorrect
*/