How to sort excel ? Topic is solved

[Marcosa1020]

Hi
When I press button submit, and then open the A.xlsx excel file at C:\Test\A.xlsx , I can get like photo_1.

Photo_1.jpg (72.89 KiB) Viewed 1613 times

Could you tell me how can I to sort A.xlsx from column A2 to L999 by date and order by newest to oldest , when I open the A.xlsx file before ?
Just like photo_2. Thank a lot.

Photo_2.jpg (71.5 KiB) Viewed 1613 times

Code: Select all

#NoTrayIcon
Gui +AlwaysOnTop
Gui, Color, 111111
Gui, Font, S10 EEAA99, Tahoma
Gui, Show, x131 y91 h210 w218,Program1
Gui, Add, Text, x5 y15 cFFCC00, Item1
Gui, Font, S14 EEAA99, Tahoma
Gui, Add, Edit, x43 y10 w170 h25 vCard1
Gui, Font, S10 EEAA99, Tahoma
Gui, Add, Text, x5 y48 cFFCC00, Item2
Gui, Add, Edit, x43 y44 w170 h25 vCard2
Gui, Add, Text, x5 y81 cFFCC00, No
Gui, Add, Edit, x43 y78 w170 h25 vCard6
Gui, Add, Text, x5 y116 cFFCC00, Item3
Gui, Add, DropDownList, x43 y112 w70 h200 vCard3, A|B|C
Gui, Add, Text, x120 y116 cFFCC00, Item4
Gui, Add, DropDownList, x155 y112 w58 h200 vCard4, 01|02
Gui, Add, Text, x5 y149 cFFCC00, Item5
Gui, Add, DropDownList, x43 y145 w70 h200 vCard5, S1|T1
Gui, Add, Button, x5 y179 w40 h24 cFFCC00,Web
Gui, Add, Button, x122 y145 w90 h24 cFFCC00, Submit
Send {Tab}

#Persistent
WinGet, a_ID, ID, A
SetTimer, aa, 500
return

aa:
WinGet, a1_ID, ID, A
If ( a_ID != a1_ID )
k_ID = %a1_ID%
Return

ButtonSubmit:
Gui, Submit, NoHide
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\Test\A.xlsx")
wb1 := xl.Workbooks.Open("C:\Test\A.csv") 
lRow := wb0.ActiveSheet.Range("F1048576").End(-4162).Row   
aRow := wb1.ActiveSheet.Range("A1048576").End(-4162).Row  
wb1.ActiveSheet.Range("A3:E15").copy(wb0.ActiveSheet.Cells(lRow+1, 8))  
wb1.ActiveSheet.Range("A1").copy(wb0.ActiveSheet.Cells(lRow+1, 7))  

FormatTime, TimeString,,M/d/yy HH:mm
Loop % aRow -2  
{
	offset := A_Index
	wb0.ActiveSheet.Cells(lRow+offset, 1).value := TimeString
	wb0.ActiveSheet.Cells(lRow+offset, 2).value := card5
	wb0.ActiveSheet.Cells(lRow+offset, 3).value := card3 card4
	wb0.ActiveSheet.Cells(lRow+offset, 4).value := card2
	wb0.ActiveSheet.Cells(lRow+offset, 5).value := card1
	wb0.ActiveSheet.Cells(lRow+offset, 6).value := card6
}
wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
xl := ""

Msgbox, 64, Message, Completed, 500
GuiControl,,card1,
GuiControl,,card2,
GuiControl,,card3,|
GuiControl,,card3, A|B|C
GuiControl,,card4,|
GuiControl,,card4, 01|02
GuiControl,,card5,|
GuiControl,,card5, S1|T1
Send {Tab}{Tab}
return

return
GuiClose:
ExitApp

[FanaticGuru]

Only glanced at your code but maybe this will help:

Code: Select all

wb1.ActiveSheet.Range("A2:L999").Sort()

I believe the defaults will get you what you want but here are all the parameters that can be used.

https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

FG

[Marcosa1020]

Only glanced at your code but maybe this will help:

Code: Select all

wb1.ActiveSheet.Range("A2:L999").Sort()

I believe the defaults will get you what you want but here are all the parameters that can be used.

https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

FG

Hi,

I tried to run these codes as below, but it can not be running.
wb0.ActiveSheet.Range("A2:L999").Sort() ; I have changed wb1 to wb0, because the FormatTime in A.xlsx
wb0.ActiveSheet.Range("A2:L999").Sort("A1")

[FanaticGuru]

I tried to run these codes as below, but it can not be running.
wb0.ActiveSheet.Range("A2:L999").Sort() ; I have changed wb1 to wb0, because the FormatTime in A.xlsx
wb0.ActiveSheet.Range("A2:L999").Sort("A1")

The MSDN says all the parameters are optional which I assumed would make it sort by the first column but you appear to have to define the range to sort by.

Here is a tested example that works:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.Range("A1:B10").Sort(xlApp.Range("A1"))

FG

[FanaticGuru]

I tried to run these codes as below, but it can not be running.
wb0.ActiveSheet.Range("A2:L999").Sort() ; I have changed wb1 to wb0, because the FormatTime in A.xlsx
wb0.ActiveSheet.Range("A2:L999").Sort("A1")

The MSDN says all the parameters are optional which I assumed would make it sort by the first column but you appear to have to define the range to sort by.

Here is a tested example that works:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.Range("A1:B10").Sort(xlApp.Range("A1"))

FG

[Marcosa1020]

I tried to run these codes as below, but it can not be running.
wb0.ActiveSheet.Range("A2:L999").Sort() ; I have changed wb1 to wb0, because the FormatTime in A.xlsx
wb0.ActiveSheet.Range("A2:L999").Sort("A1")

The MSDN says all the parameters are optional which I assumed would make it sort by the first column but you appear to have to define the range to sort by.

Here is a tested example that works:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.Range("A1:B10").Sort(xlApp.Range("A1"))

FG

Hi FanaticGuru,

Thank you so much.