HMG EXCEL OLE FUNCTIONS
Moderator: Rathinagiri
HMG EXCEL OLE FUNCTIONS
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
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 370 times
Re: HMG EXCEL OLE FUNCTIONS
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"))
*********************************************************************************************************
- mustafa
- Posts: 1160
- Joined: Fri Mar 20, 2009 11:38 am
- DBs Used: DBF
- Location: Alicante - Spain
- Contact:
Re: HMG EXCEL OLE FUNCTIONS
Hi Tiger
thanks for the contribution
regards
Mustafa
thanks for the contribution
regards
Mustafa
Re: HMG EXCEL OLE FUNCTIONS
Hi Tiger
Many thanks for this unique information, which is difficult to obtain otherwise.
rpc
Many thanks for this unique information, which is difficult to obtain otherwise.
rpc
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: HMG EXCEL OLE FUNCTIONS
I have an EXCEL question.
Witihin my application I do this:
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
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)
How to avoid this ?
Serge
There's nothing you can do that can't be done...
- AUGE_OHR
- Posts: 2064
- Joined: Sun Aug 25, 2019 3:12 pm
- DBs Used: DBF, PostgreSQL, MySQL, SQLite
- Location: Hamburg, Germany
Re: HMG EXCEL OLE FUNCTIONS
i do this under Xbase++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 ?
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
have fun
Jimmy
Jimmy
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: HMG EXCEL OLE FUNCTIONS
Hi Jimmy,
Thx, I will give it a try!
Serge
Thx, I will give it a try!
Serge
There's nothing you can do that can't be done...
Re: HMG EXCEL OLE FUNCTIONS
Hi,
Please add oExcel:Quit() as the last line.
Warm regards,
Jayadev
Please add oExcel:Quit() as the last line.
Warm regards,
Jayadev
- serge_girard
- Posts: 3167
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: HMG EXCEL OLE FUNCTIONS
Thanks Jimmy & Jayadev,
That was the solution I searched for!
Serge
That was the solution I searched for!
Serge
There's nothing you can do that can't be done...