However, it is not always easy to translate the Excel Object Model to HMG. In the code below you will find some more elaborate examples
on how to open a sheet, select a worksheet, save a sheet, write in tabs, change color, pattern & borders of cells, etc.
You can rem out/in any part of code you like.
Maybe it can be useful for you too!
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
*/