Sort Excel Column

mwiger

Forums

Hello all,
 
I have been trying to sort a column from an excel file.  I have a bit of code that is able to do number formatting as well as other formatting but I cant find anything that is for sorting.  The one thing that I did find wants to use Range and I am getting errors when I run the script.
 
Here is what I have so far.  I have also tried to use oSheet.Range.  Any help would be greatly appreciated.  Thanks!
 
Set oSheet = oBook.Worksheets.Item(1)
      oSheet.Columns("C").NumberFormat = "_(#,##0.00_);_((#,##0.00);_("" - ""??_);_(@_)"    
      oSheet.Columns("A:C").sort key1:=Range("C"), order1:=Descending, Header:=xlYes  
Set oSheet = Nothing

Bert_B Mon, 10/11/2021 - 03:19

Hi,
 
 
I don't know if that is an accetable solution for you, but you can use the build-in Python functionality from IDEA to do this.
 
 
1. You open the editor (txt file) and paste the following code into it, adjusting the folder where your excel-file lies:
 
import win32com.client
wbk = 'C:\\myfolder\\myfile.xlsx'
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Workbooks.Open(wbk)
xlAscending = 1
xlSortColumns = 1
xlApp.Sheets(1).Range("A2:C9").Sort(Key1=xlApp.Sheets(1).Range("C1"), Order1=xlAscending,
Orientation=xlSortColumns)
xlApp.Quit
xlApp = None
 
 
I also attached a txt editor file with the code.  Don't forget to change the file extension from txt to py. Within the code you have to adjust the path to your file, the filename and the range you want to sort. In this case it sorts the range A2:C9 according to column C ascending. (If you have no header you might want to start at A1.)
 
2. You save this txt as a .py file
3. You open IDEA and under ribbon macro you click on execute. In the window which opens you have to adjust the file type to not only display .iss files, or exe files, but also .py files.
4. Select your py file and click on ok. IDEA should silently execute the file and the xlsx file should be sorted.