Excel macro to autoit code

[nirious]

Columns("A:M").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("M2:M809") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:M809")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Hi,

 

I can't get this vba code that I got from an excel macro to work with autoit.

[FireFox]

Hi,

Welcome to the autoit forum

Can you show us what you've tried so far ? Or you expect us to do it for you ? 

Br, FireFox.

[water]

Please have a look at the latest AutoIt beta version (3.3.11.4). it comes with a sort function (_Excel_RangeSort) that should do what you need.

[nirious]

Thank you for the kind welcome.

I need to automate some actions on an excel file outside of excel.

So I came across autoit

Everything seems to be kind of straight foreward but I was struggeling to transform that := property notation to something that is accepted by autoit.

I need to keep the header of a file and sort all other rows based on column M.

This is what I had so far (part of it is commented out )

• treating the := as a property
• treating the whole thing as a single string
• with or without ()

$exlYes=1
$exlTopToBottom=1
$exlPinYin=1
$exlSortOnValues=0
$exlAscending=1
$exlSortNormal=0
$exlSortColumns=1

;$oExcel1.ActiveSheet.Columns("A:M").Select
;$oExcel1.ActiveSheet.Sort.SortFields.Clear
;$oExcel1.ActiveSheet.Sort.SortFields.Add('Key:=Range("M2:M"' & $iLastLineNr & '), SortOn:=' & $xlSortOnValues & ', Order:=' & $xlAscending & ', DataOption:=' & $xlSortNormal)
;$oExcel1.ActiveSheet.Sort.SetRange($oExcel1.ActiveSheet.Range("A1:M" & $iLastLineNr))
;$oExcel1.ActiveSheet.Sort.Header = $exlYes
;$oExcel1.ActiveSheet.Sort.MatchCase = False
;$oExcel1.ActiveSheet.Sort.Orientation = $exlTopToBottom
;$oExcel1.ActiveSheet.Sort.SortMethod = $exlPinYin
;$oExcel1.ActiveSheet.Sort.Apply

Though I finally managed to get it working by extending a piece of code I found via google and figuring out how to put in the unused properties in the function.

It has a totally different approach, but the end result is the same.

$oExcel1.ActiveSheet.Range("M1").Sort($oExcel1.ActiveSheet.Range("M:M"), $exlAscending, Null, Null, $exlAscending, Null, $exlAscending, $exlYes, Null, False, $exlSortColumns, $exlPinYin, $exlSortNormal, $exlSortNormal, $exlSortNormal)

I wasn't aware of the _Excel_RangeSort function.

Though I still would like to know what the autoit alternative is for the vba := notation.

[water]

Though I still would like to know what the autoit alternative is for the vba := notation.

AutoIt doesn't support named parameters.

"Key" is the first parameter in the parameter list. So simply drop the "Key:=" part and only add the value of the parameter e.g. Range("M2:M809")

Replace parameters you don't know with the "Default" keyword.

Details can be found here.