Sign in to follow this  
Followers 0
nirious

Excel macro to autoit code

5 posts in this topic

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.

Share this post


Link to post
Share on other sites



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 ?  :lol:

Br, FireFox.


 

OS : Win XP SP2 (32 bits) / Win 7 SP1 (64 bits) / Win 8 (64 bits) | Autoit version: latest stable / beta.
Hardware : Intel(R) Core(TM) i5-2400 CPU @ 3.10Ghz / 8 GiB RAM DDR3.

My UDFs : Skype UDF | TrayIconEx UDF | GUI Panel UDF | Excel XML UDF | Is_Pressed_UDF

My Projects : YouTube Multi-downloader | FTP Easy-UP | Lock'n | WinKill | AVICapture | Skype TM | Tap Maker | ShellNew | Scriptner | Const Replacer | FT_Pocket | Chrome theme maker

My Examples : Capture toolIP Camera | Crosshair | Draw Captured Region | Picture Screensaver | Jscreenfix | Drivetemp | Picture viewer

My Snippets : Basic TCP | Systray_GetIconIndex | Intercept End task | Winpcap various | Advanced HotKeySet | Transparent Edit control

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thank you for the kind welcome.

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

So I came across autoit :bye:

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0