Page 1 of 1
HMG EXCEL OLE FUNCTIONS
Posted: Wed Sep 09, 2020 6:54 am
by Tiger
Re: HMG EXCEL OLE FUNCTIONS
Posted: Wed Oct 07, 2020 2:36 am
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"))
*********************************************************************************************************
Re: HMG EXCEL OLE FUNCTIONS
Posted: Wed Oct 07, 2020 11:26 am
by mustafa
Hi Tiger
thanks for the contribution
regards
Mustafa
Re: HMG EXCEL OLE FUNCTIONS
Posted: Wed Oct 07, 2020 5:48 pm
by RPC
Hi Tiger
Many thanks for this unique information, which is difficult to obtain otherwise.
rpc
Re: HMG EXCEL OLE FUNCTIONS
Posted: Fri Oct 09, 2020 4:08 pm
by mol
THX!
Re: HMG EXCEL OLE FUNCTIONS
Posted: Mon Oct 12, 2020 4:44 pm
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
Re: HMG EXCEL OLE FUNCTIONS
Posted: Mon Oct 12, 2020 8:45 pm
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
Re: HMG EXCEL OLE FUNCTIONS
Posted: Tue Oct 13, 2020 6:37 am
by serge_girard
Hi Jimmy,
Thx, I will give it a try!
Serge
Re: HMG EXCEL OLE FUNCTIONS
Posted: Tue Oct 13, 2020 11:59 am
by jayadevu
Hi,
Please add oExcel:Quit() as the last line.
Warm regards,
Jayadev
Re: HMG EXCEL OLE FUNCTIONS
Posted: Tue Oct 13, 2020 2:05 pm
by serge_girard
Thanks Jimmy & Jayadev,
That was the solution I searched for!
Serge