HMG EXCEL OLE FUNCTIONS

Topic Specific Tutorials and Tips.

Moderator: Rathinagiri

Post Reply
Tiger
Posts: 68
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 27 times

HMG EXCEL OLE FUNCTIONS

Post by Tiger »

Hi,

Attached file include some codes to use HMG OLE function to create excel charts, FYI.

You can refer to following links to get the detail ....

I use the samples there and modify them to be worked with HMG ...

https://www.excelanytime.com/excel/inde ... Itemid=475
https://www.excelanytime.com/excel/inde ... Itemid=475
https://www.excelanytime.com/excel/inde ... Itemid=475
https://www.excelanytime.com/excel/inde ... Itemid=475
https://www.excelanytime.com/excel/inde ... Itemid=475
https://www.excelanytime.com/excel/inde ... Itemid=475
Attachments
_XLS_OLE_SAMPLES.rar
(151.69 KiB) Downloaded 133 times

Tiger
Posts: 68
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 27 times

Post by Tiger »

More Excel OLE codes ...

Code: Select all

*********************************************************************************************************
COMMENTS

  OldComment := Sheet1:Range("A1"):Comment:Text  // GET COMMENT
  NewComment := OldComment + " Edited comment"
  Sheet1:Range("A1"):Comment:Delete() // DELETE COMMENT
  Sheet1:Range("A1"):AddComment (NewComment) // ADD COMMENT

*********************************************************************************************************
outline

oExcel:Selection:ClearOutline()     // disable outline

*********************************************************************************************************
Freeze Panes

oSheet:cells(2,11):SELECT()      
oExcel:ActiveWindow:FreezePanes := .T.  // Enable
oExcel:ActiveWindow:FreezePanes := .F.  // Disable
*********************************************************************************************************
Auto Filter        // AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)

A. oSheet:Range("A1:U1"):AutoFilter() // ENABLE

B. oSheet:Range("A1:U1"):AutoFilter(8,"RED") // ENABLE AND SET Criteria1 ==> (Field, Criteria1)

C. oSheet:Range("A1"):AutoFilter(8,"RED",xlOr,"BLUE")  //xlOr ==> (Field, Criteria1, Operator, Criteria2) 

D. oExcel:Worksheets("AOI_DAILY"):Range("A1"):AutoFilter(11,">70",xlAnd, "<320")  // xlAnd ==> (Field, Criteria1, Operator, Criteria2) 

oExcel:Selection:AutoFilter() // Disable
 
********************************************************************************************************* 
Sort               // SortFields:Add(KEY、 SortOn、 Order、 CustomOrder, DataOption)

cRange := "P1" 
_1st_Range := oExcel:Sheets("AOI_DAILY"):Range( cRange ) // SET RANG of 1ST KEY

cRange := "E1:E924" 
_2nd_Range := oExcel:Sheets("AOI_DAILY"):Range( cRange ) // SET RANG of 2ND KEY
   
cRange := "F1:F924" 
_3rd_Range := oExcel:Sheets("AOI_DAILY"):Range( cRange ) // SET RANG of 3RD KEY

cRange := "J1:J924" 
_4th_Range := oExcel:Sheets("AOI_DAILY"):Range( cRange ) // SET RANG of 4TH KEY

cRange := "A1:U924" 
SORT_Range := oExcel:Sheets("AOI_DAILY"):Range( cRange ) // SET RANG of SORT Area

With OBJECT oExcel:Worksheets("AOI_DAILY"):Sort()
   :SortFields:Clear()
   :SortFields:Add(_1st_Range,xlSortOnCellColor,xlDescending,xlSortNormal):SortOnValue:Color:=RGB(255, 255, 0) // BY Cell Color & Descending
   :SortFields:Add(_2nd_Range,xlSortOnValues,xlAscending,xlSortNormal)
   :SortFields:Add(_3rd_Range,xlSortOnValues,xlAscending,xlSortTextAsNumbers) // Text As Numbers
   :SortFields:Add(_4th_Range,xlSortOnFontColor,xlAscending,xlSortNormal):SortOnValue:Color:=RGB(0, 0, 0) // BY FONT Color
   :SetRange(SORT_Range)
   :Header := xlYes
   :MatchCase := .F.
   :Orientation :=xlTopToBottom
   :SortMethod := xlPinYin
   :Apply()
EndWith
 
*********************************************************************************************************
Merge CELLS

A. oSheet:Range("A1:D1"):Merge()

.OR. 

B. 
   cRange := "A16:B17" 
   oSheet:Range(cRange):Select()
   oExcel:Application:CutCopyMode:= .F.
   with OBJECT oExcel:Selection()
        :HorizontalAlignment := xlCenter
        :VerticalAlignment := xlCenter
        :WrapText := .F.
        :Orientation := 0
        :AddIndent := .F.
        :IndentLevel := 0
        :ShrinkToFit := .F.
        :ReadingOrder := xlContext
        :MergeCells := .F.
   EndWith
   oExcel:Selection:Merge()

*********************************************************************************************************   
Check if merged

If oSheet:Range("A22"):MergeCells  // .OR. If oSheet:Range("A22:B22"):MergeCells // .OR. If oSheet:Cells(22, 1):MergeCells
   oSheet:cells(T,1):SELECT() // SET activecell
   // .OR. oSheet:Range("A25"):SELECT()                         // SET activecell
   MSGINFO(oExcel:Activecell:MergeArea:Rows:Count())    // How many rows are merged?
   MSGINFO(oExcel:Activecell:MergeArea:Columns:Count()) // How many columns are merged?
   MSGINFO(oExcel:Activecell:MergeArea:Cells:Count())   // How many cells are merged?
   MSGINFO(oSheet:Range("A25"):VALUE())
   MSGINFO(oExcel:activecell:MergeArea:Address())       // What's the merged range address?
EndIf

********************************************************************************************************* 
MOVE SHEET

  oExcel:Application:Worksheets("Sheet1"):Move(after:=oExcel:Application:Worksheets("Sheet3"))  
  oExcel:Worksheets("Sheet2"):Move(before:=oExcel:Worksheets("AOI_DAILY"))  
  oExcel:Worksheets("Sheet3"):Move(after:=oExcel:Worksheets(oExcel:Sheets:Count()))
  oExcel:sheets("Sheet3"):Move(after:=oExcel:sheets(oExcel:Sheets:Count()))

ADD SHEET

  oExcel:Sheets:Add:Name:=oSheet:range("a3"):value()  //add a Sheet before the ActiveSheet
  oExcel:Worksheets:Add(after:=oExcel:Worksheets("Sheet5")):Name:="NewSheet" 
  oExcel:Worksheets:Add(before:=oExcel:Worksheets("Sheet5")):Name:=oSheet:range("a3"):value()
  oExcel:sheets():Add(after:=oExcel:sheets("Chart2")):Name:="NewSheet"  // sheets() CAN BE USED WITH "CHART" OR "SHEET"
  
COPY SHEET
  
  oExcel:Worksheets("AOI_DAILY"):Copy(before:=oExcel:Worksheets("Sheet5")) 
  oExcel:Worksheets("AOI_DAILY"):Copy(after:=oExcel:Worksheets(oExcel:Sheets:Count())) 
  oExcel:sheets("AOI_DAILY"):Copy(after:=oExcel:sheets("Sheet3")) 
  oExcel:ActiveSheet:Name:="MySheetName"  // ASSIGN NAME

*********************************************************************************************************
PasteSpecial       // Paste Formulas/Formats

  oSheet:Range("D2:E2"):COPY()	
  cRange:="D3:E30" 
  oSheet:Range(cRange):Select()
  oExcel:Selection:PasteSpecial(xlPasteFormulas,xlPasteSpecialOperationNone) // Paste Formulas
  oExcel:Selection:PasteSpecial(xlPasteFormats,xlPasteSpecialOperationNone)  // Paste Formats
  oExcel:Selection:PasteSpecial(Paste:=xlPasteFormats,Operation:=xlPasteSpecialOperationNone)
*********************************************************************************************************
AutoFill

A. Define Rang 
   sourceRange := oSheet:Range("I2:I2") 
   fillRange   := oSheet:Range("I2:I30") 

.OR.  

   sourceRange := oExcel:Sheets( "AOI_DAILY" ):Range("I2:I2") 
   fillRange   := oExcel:Sheets( "AOI_DAILY" ):Range("I2:I20") 
  
B. AutoFill the Rang   
  
   sourceRange:AutoFill(Destination:=fillRange,TYPE:=xlFillFormats)
  
.OR. 

   oSheet:Range("I2:I2"):AutoFill(fillRange,xlFillFormats)

*********************************************************************************************************
GET SHHETs / CHARTs NAME
 
 ws:=oExcel:sheets()
 
 wc:=oExcel:Charts()

A. Returns Only the names of each worksheets  
 
   MSGINFO(oExcel:Worksheets:Count())
 
   For Each ws In oExcel:Worksheets() 
       MSGINFO(ws:Name())  
   Next  
   
   For i = 1 To oExcel:Worksheets:Count()
       MSGINFO(oExcel:Worksheets(i):Name())  
   Next i

B. Returns the names of each worksheets & Charts  

   MSGINFO(oExcel:Sheets:Count()) 
 
   For Each ws In oExcel:Sheets 
       MSGINFO(ws:Name())  
   Next 
   
   For i = 1 To oExcel:Sheets:Count()
       MSGINFO(oExcel:sheets(i):Name())  
   Next i

C. Returns Only the names of each Charts  

   MSGINFO(oExcel:Charts:Count())

   For Each wc In oExcel:Charts()  
       MSGINFO(wc:Name())  
   Next

   For i = 1 To oExcel:Charts:Count()
       MSGINFO(oExcel:Charts(i):Name())  
   Next i
********************************************************************************************************* 
Chart Add, Copy & Move

 oExcel:Charts:Add(After:=oExcel:Charts("NEW CHART 2")):Name:="NEW CHART 3"
 oExcel:Charts:Add(before:=oExcel:Worksheets("Sheet3")):Name:=oSheet:range("C3"):value()

 oExcel:Charts("NEW CHART 2"):Copy(after:=oExcel:sheets("NEW CHART 1")) 
 oExcel:ActiveSheet:Name = "My new Chart" 
 
 oExcel:Charts("NEW CHART 2"):Move(before:=oExcel:Charts("NEW CHART 1")) 

*********************************************************************************************************

User avatar
mustafa
Posts: 1032
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Been thanked: 234 times
Contact:

Post by mustafa »

Hi Tiger
thanks for the contribution
regards

Mustafa

RPC
Posts: 237
Joined: Fri Feb 10, 2017 4:12 am
DBs Used: DBF
Has thanked: 124 times
Been thanked: 13 times

Post by RPC »

Hi Tiger
Many thanks for this unique information, which is difficult to obtain otherwise.
rpc


User avatar
serge_girard
Posts: 2636
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 911 times
Been thanked: 166 times
Contact:

Post by serge_girard »

I have an EXCEL question.

Witihin my application I do this:

Code: Select all

oExcel1 := CreateObject( "Excel.Application" )
oSheet1 := oExcel1:ActiveSheet()
oSheet1:Cells:Font:Name := "Arial"
oSheet1:Cells:Font:Size := 12
oSheet1:Cells( 1, 1 ):Value := "FIL"
oSheet1:Cells( 1, 1 ):Font:Size := 16
etc..
oSheet1:Cells( 1, 1 ):Select()
oExcel1:Visible := .T.
oExcel1:DisplayAlerts := 0 
oSheet1:SaveAs(cFILE_NAME) 
When it is running EXCEL starts and shows my sheet. Then I close BUT taskmanagers shows EXCEL still as running...!
How to avoid this ?

Serge

User avatar
AUGE_OHR
Posts: 1149
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 213 times
Been thanked: 310 times

Post by AUGE_OHR »

serge_girard wrote:
Mon Oct 12, 2020 4:44 pm
When it is running EXCEL starts and shows my sheet. Then I close BUT taskmanagers shows EXCEL still as running...!
How to avoid this ?
i do this under Xbase++

Code: Select all

            oExcel:application:workbooks( 1 ) :saveas( zPath + "JOBKDOPL.XLS" )

            // Quit Excel
            oExcel:Quit()

            // destroy the reference
            oExcel:destroy()
            IF ComLastError() > 0
               MSGBOX( "Error: " + STR( ComLastError() ) + CHR( 13 ) + CHR( 10 ) + "Description:" + ComLastMessage() )
            ENDIF
under harbour i found no Destroy() so just try to use NIL
have fun
Jimmy

User avatar
serge_girard
Posts: 2636
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 911 times
Been thanked: 166 times
Contact:

Post by serge_girard »

Hi Jimmy,
Thx, I will give it a try!
Serge

jayadevu
Posts: 220
Joined: Tue May 19, 2009 7:10 am
Been thanked: 10 times

Post by jayadevu »

Hi,

Please add oExcel:Quit() as the last line.

Warm regards,

Jayadev

User avatar
serge_girard
Posts: 2636
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 911 times
Been thanked: 166 times
Contact:

Post by serge_girard »

Thanks Jimmy & Jayadev,

That was the solution I searched for!

Serge

Post Reply