Some Elaboration on Excel functions (OLE)

Topic Specific Tutorials and Tips.

Moderator: Rathinagiri

User avatar
Clip2Mania
Posts: 99
Joined: Fri Jun 13, 2014 7:16 am
Location: Belgium

Some Elaboration on Excel functions (OLE)

Post by Clip2Mania »

I found some good information on this forum and in the Samples (C:\hmg.3.3.1\SAMPLES\Controls\OLE)
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
*/
Attachments
ExcelTest.zip
(2.51 KiB) Downloaded 898 times
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: Some Elaboration on Excel functions (OLE)

Post by Rathinagiri »

Wonderful and very useful. Thank you very much.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: Some Elaboration on Excel functions (OLE)

Post by andyglezl »

Gracias por la inf.
Andrés González López
Desde Guadalajara, Jalisco. México.
Javier Tovar
Posts: 1275
Joined: Tue Sep 03, 2013 4:22 am
Location: Tecámac, México

Re: Some Elaboration on Excel functions (OLE)

Post by Javier Tovar »

Hola Clip2Mania,

Compile tu demo y me da este error:
ErrorOLE.jpg
ErrorOLE.jpg (71.91 KiB) Viewed 9620 times
Hay que tener alguna consideración al respecto?

Saludos
User avatar
Clip2Mania
Posts: 99
Joined: Fri Jun 13, 2014 7:16 am
Location: Belgium

Re: Some Elaboration on Excel functions (OLE)

Post by Clip2Mania »

Hi Javier,
Compile tu demo y me da este error:
What version of Excel are you using?
I've tested this on Excel 2010 & Win 7 Pro 32 bits and it's working just fine.
The "Worksheets("name") function has not changed in later version, as I recall...
User avatar
bpd2000
Posts: 1207
Joined: Sat Sep 10, 2011 4:07 am
Location: India

Re: Some Elaboration on Excel functions (OLE)

Post by bpd2000 »

Working in Xp and Excel-2007
Last edited by bpd2000 on Sat Sep 20, 2014 6:18 am, edited 1 time in total.
BPD
Convert Dream into Reality through HMG
User avatar
Clip2Mania
Posts: 99
Joined: Fri Jun 13, 2014 7:16 am
Location: Belgium

Re: Some Elaboration on Excel functions (OLE)

Post by Clip2Mania »

Working in Xp and word-2007
Word? I guess you mean "Excel"? :)
The Excel 2007 Object Model already has the concept of Worksheets. I cannot test it (because I don't have Excel 2007 nor WinXP), but you can try & change Worksheets("Sheet2") simply by Worksheets(2), as far as I can see from the Excel 2007 "Worksheets" Object
Javier Tovar
Posts: 1275
Joined: Tue Sep 03, 2013 4:22 am
Location: Tecámac, México

Re: Some Elaboration on Excel functions (OLE)

Post by Javier Tovar »

Yo estoy utilizando Win7 32 bits y Office 2010 y HMG3.3.1

Y me da error como lo mostre en el anterior post.

Saludos
User avatar
Clip2Mania
Posts: 99
Joined: Fri Jun 13, 2014 7:16 am
Location: Belgium

Re: Some Elaboration on Excel functions (OLE)

Post by Clip2Mania »

:idea: When you open Excel manually, does it has "Sheet1","Sheet2","Sheet3" on the bottom?
If you don't have an English version of Office, you have to replace "Sheet2" with your "local language version" of it...
User avatar
LOUIS
Posts: 203
Joined: Tue Dec 11, 2012 9:05 pm
DBs Used: DBF

Re: Some Elaboration on Excel functions (OLE)

Post by LOUIS »

Ya lo probé, y Sí funciona en XP y Excel 2007

Sólo hice 2 cambios:
A la fórmula sum le agregué la letra A, y
cambié Sheet2 por Hoja2

Thanks for share Cilp2mania ;)
Post Reply