| Author | Topic: Sort Excel Sheet with ActiveX |
|---|
 | César Calvo | Sort Excel Sheet with ActiveX
on Tue, 25 Apr 2017 20:12:10 +0200I am trying to sort for an excel sheet and if I make a macro the code is:
ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("A2:A568")
_
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Hoja1").Sort
.SetRange Range("A1:F568")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
But I have no idea that to translate this code to sorce code of Alaska.
Could somebody help me?
Thanks.
César. |
| Jonathan Leeming | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (0/1)
on Tue, 25 Apr 2017 15:28:22 -0600Hi César,
I have never tried sorting before but thought I would give it a try.
I have attached a very small prg that contains a function that...
1. Opens an existing Excel Spreadsheet
2. Sorts a range "A4:K29" in descending order based upon values in
Range "J4:J29"
3. Saves & Closes the file.
Hopfully this will aid you in translating your macro code.
Just remember that the period (.) is replaced with colons ( and that
methods have () after them.
I also find that the following link is very handy when working with
Xbase++ & Excel...
https://msdn.microsoft.com/en-us/library/office/ee861528.aspx
Cheers... Jonathan
On Tue, 25 Apr 2017 20:12:10 +0200, César Calvo
<ccalvoc@telefonica.net> wrote:
>I am trying to sort for an excel sheet and if I make a macro the code is:
>
>ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("A2:A568")
>_
> , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
>With ActiveWorkbook.Worksheets("Hoja1").Sort
> .SetRange Range("A1:F568")
> .Header = xlYes
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
>End With
>
>But I have no idea that to translate this code to sorce code of Alaska.
>Could somebody help me?
>Thanks.
>César. |
| Jonathan Leeming | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (0/1)
on Tue, 25 Apr 2017 15:42:06 -0600Hi Again,
If you need to create an Excel.ch header file...
tlb2ch Excel.Application.15 /o:excel.ch
The current version (15) is obtained from the registry entry...
HKEY_CLASSES_ROOT\Excel.Application\CurVer
Cheers... Jonathan
On Tue, 25 Apr 2017 15:28:22 -0600, Jonathan Leeming wrote:
>Hi César,
>
>I have never tried sorting before but thought I would give it a try.
>
>I have attached a very small prg that contains a function that...
> 1. Opens an existing Excel Spreadsheet
> 2. Sorts a range "A4:K29" in descending order based upon values in
>Range "J4:J29"
> 3. Saves & Closes the file.
>
>Hopfully this will aid you in translating your macro code.
>
>Just remember that the period (.) is replaced with colons ( and that
>methods have () after them.
>
>I also find that the following link is very handy when working with
>Xbase++ & Excel...
>
>https://msdn.microsoft.com/en-us/library/office/ee861528.aspx
>
>Cheers... Jonathan
>
>On Tue, 25 Apr 2017 20:12:10 +0200, César Calvo
><ccalvoc@telefonica.net> wrote:
>
>>I am trying to sort for an excel sheet and if I make a macro the code is:
>>
>>ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("A2:A568")
>>_
>> , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
>>With ActiveWorkbook.Worksheets("Hoja1").Sort
>> .SetRange Range("A1:F568")
>> .Header = xlYes
>> .MatchCase = False
>> .Orientation = xlTopToBottom
>> .SortMethod = xlPinYin
>> .Apply
>>End With
>>
>>But I have no idea that to translate this code to sorce code of Alaska.
>>Could somebody help me?
>>Thanks.
>>César. |
| Jonathan Leeming | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (1/1)
on Tue, 25 Apr 2017 15:28:23 -0600 |
| César Calvo | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (1/1)
on Wed, 26 Apr 2017 06:33:11 +0200 |
| Jonathan Leeming | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (1/1)
on Wed, 26 Apr 2017 08:48:38 -0600Hi César,
Here is the code from the prg...
FUNCTION ExcelSortTest()
LOCAL oXls,oBook,oSheet,orange,oSelect,oSort
#DEFINE xlSortOnValues 0
#DEFINE xlAscending 1
#DEFINE xlDescending 2
#DEFINE xlGuess 0
#DEFINE xlTopToBottom 1
#DEFINE xlPinYin 1
oXls := CreateObject("Excel.Application") Open Excel
oBook := oXls:Workbooks:Open("C:\famxbase\TestData.xlsx")
oSheet := oXls:Sheets("Details")
oSort := oSheet:Sort
oSort:SortFields:Clear()
oSort:SortFields:Add(oSheet:Range("J4:J29"),xlSortOnValues,xlDescending)
oSort:SetRange(oSheet:Range("A4:K29"))
oSort:Header = xlGuess
oSort:MatchCase = .F.
oSort:Orientation = xlTopToBottom
oSort:SortMethod = xlPinYin
oSort:Apply()
oBook:Save()
oBook:Close()
oXls:quit()
oXls:destroy()
RETURN NIL
Please let me know how you make out and if you need further
clarification.
Regards... Jonathan
On Wed, 26 Apr 2017 06:33:11 +0200, César Calvo
<ccalvoc@telefonica.net> wrote:
>Hello Jonathan.
>The attachment file is not visualized.
>Thanks.
>César.
>
>"Jonathan Leeming" escribió en el mensaje de noticias:nrfvfcd1ithcbftt0o76r03mpmkol3f6rn@4ax.com... |
| César Calvo | Re: Sort Excel Sheet with ActiveX - ExcelSort.prg (1/1)
on Wed, 26 Apr 2017 22:24:20 +0200Many thanks Jonata, it works fine.
METHOD PRUEBA CLASS PROCESO
LOCAL oXls,oBook,oSheet,orange,oSelect,oSort, cFil, B, cVal
MenuSelect(ID_MNU_ESPERAR,SDI_VIEW_ESPERAR)
cFil := cDir + "CARGA_MASIVA\PROVISIONES\" + 'PROV_F1F9.xlsx'
oXls := CreateObject("Excel.Application") Open Excel
oXls:DisplayAlerts := .F.
oXls:visible := .F.
oBook := oXls:Workbooks:Open(cFil)
oSheet := oXls:Sheets("Hoja1")
oSort := oSheet:Sort
oSort:SortFields:Clear()
oSort:SortFields:Add(oSheet:Range("A1"),xlSortOnValues,xlDescending)
oSort:SetRange(oSheet:Range("A2:F1000"))
oSort:Header = xlGuess
oSort:MatchCase = .F.
oSort:Orientation = xlTopToBottom
oSort:SortMethod = xlPinYin
oSort:Apply()
oXls:Range("A2:F1000"):AutoFilter(2, "0,00")
oXls:Selection:Delete
oXls:Cells(1,1):Select
oBook:Save()
oBook:Close()
oXls:quit()
oXls:destroy()
MenuSelect(ID_MNU_CLOSE,SDI_VIEW_CLOSE)
RETURN self
////////////////////////////////////
Now I am trying to delete rows with a filter but when I do this the header
is deleted and I don´t want delete the header.
In sort I will send a code, if you have an idea, thanks.
Regards.
//////////////////////////////////
"Jonathan Leeming" escribió en el mensaje de
noticias:4mc1gcpr9j5qb9e4kh5mlu8chs4d1hl58p@4ax.com...
Hi César,
Here is the code from the prg...
FUNCTION ExcelSortTest()
LOCAL oXls,oBook,oSheet,orange,oSelect,oSort
#DEFINE xlSortOnValues 0
#DEFINE xlAscending 1
#DEFINE xlDescending 2
#DEFINE xlGuess 0
#DEFINE xlTopToBottom 1
#DEFINE xlPinYin 1
oXls := CreateObject("Excel.Application") Open Excel
oBook := oXls:Workbooks:Open("C:\famxbase\TestData.xlsx")
oSheet := oXls:Sheets("Details")
oSort := oSheet:Sort
oSort:SortFields:Clear()
oSort:SortFields:Add(oSheet:Range("J4:J29"),xlSortOnValues,xlDescending)
oSort:SetRange(oSheet:Range("A4:K29"))
oSort:Header = xlGuess
oSort:MatchCase = .F.
oSort:Orientation = xlTopToBottom
oSort:SortMethod = xlPinYin
oSort:Apply()
oBook:Save()
oBook:Close()
oXls:quit()
oXls:destroy()
RETURN NIL
Please let me know how you make out and if you need further
clarification.
Regards... Jonathan
On Wed, 26 Apr 2017 06:33:11 +0200, César Calvo
<ccalvoc@telefonica.net> wrote:
>Hello Jonathan.
>The attachment file is not visualized.
>Thanks.
>César.
>
>"Jonathan Leeming" escribió en el mensaje de
>noticias:nrfvfcd1ithcbftt0o76r03mpmkol3f6rn@4ax.com... |
