Jump to content
Sign in to follow this  
Juvigy

Excel AutoFilter to AutoIT

Recommended Posts

Juvigy

So here is what i want to do as in VBA;

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I tried several things but cant get that to work with the array.

For example this doesnt work:

$Array[0]=3
$Array[1]="1/0/1900 0:59:0"
$Array[2]=4
$Array[3]="1/0/1900 1:0:0"
$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,$Array)

The filter works if i do:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 1:0:0")

But sellects only one value. I would like to have all values between "1/0/1900 0:00:1" and "1/0/1900 1:00:0"

Share this post


Link to post
Share on other sites
JoHanatCent

So here is what i want to do as in VBA;

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I tried several things but cant get that to work with the array.

For example this doesnt work:

$Array[0]=3
$Array[1]="1/0/1900 0:59:0"
$Array[2]=4
$Array[3]="1/0/1900 1:0:0"
$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,$Array)

The filter works if i do:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 1:0:0")

But sellects only one value. I would like to have all values between "1/0/1900 0:00:1" and "1/0/1900 1:00:0"

So maybe:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 0:00:1"&" AND "&"1/0/1900 1:0:0")


Share this post


Link to post
Share on other sites
Juvigy

It doesnt work. Maybe because the formatting is messing it.

@Edit

Fixed it.As the column contains timedifference values like:0.01 0.04 0.03 0.02 0.03 0.03

I was formatting it as "m/d/yyyy h:mm" to be able to manually filter easily.

I calculated that "1/0/1900 1:0:0" is "0.0416666666860692" so i did:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"<= 0.0416666666860692",1,">= 0") which did the trick.

But i would like to know why this VBA worked and how i can translate it to AutoIt:

Criteria:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I got it by recording a macro and filtering values manually.

Edited by Juvigy

Share this post


Link to post
Share on other sites
PsaltyDS

I'm not finding an easy reference to the that syntax in VBA or VBScript, and it's not one I've had to use before: "Criteria:=Array()"

What does the ":=" operator signify?

Sorry if that's a stupid question.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
bogQ

I'm not finding an easy reference to the that syntax in VBA or VBScript, and it's not one I've had to use before: "Criteria:=Array()"

What does the ":=" operator signify?

Sorry if that's a stupid question.

:)

http://blog.contextures.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/#axzz1RoQssChT

Edited by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites
smartee

@PsaltyDS: By no means is that a stupid question, at first glance, from by background of pascal I blurted out in my head "assignment operator :)"

However, after some research, I realized that ":=" has a specialized meaning in VBA, it is used as a named argument operator, suited for cases where in passing parameters to functions, you want to specify exactly which parameters you wish to set, irrespective of order.

Here is a page with some examples: http://www.cpearson.com/excel/DefinedNames.aspx

Share this post


Link to post
Share on other sites
PsaltyDS

@PsaltyDS: By no means is that a stupid question, at first glance, from by background of pascal I blurted out in my head "assignment operator ;)"

However, after some research, I realized that ":=" has a specialized meaning in VBA, it is used as a named argument operator, suited for cases where in passing parameters to functions, you want to specify exactly which parameters you wish to set, irrespective of order.

Here is a page with some examples: http://www.cpearson.com/excel/DefinedNames.aspx

Ah so, very helpful and I can see that's a useful feature too!

So the first thing for @Juvigy to try is to make sure all the parameters are submitted in the correct order, because AutoIt doesn't support the parameter naming syntax. The example being translated from VBA is not concerned with correct parameter order because it uses the parameter names. For AutoIt you can't do that, so you must pass them in correct order.

:)

Edit: The correct parameter order per MSDN (at least for Excel 2007): .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

;)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
Juvigy

Hi Psalty,

I tried that and it works if i use single string value for the criteria. But if i Try to convert this:

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

It doesnt work. And i dont understand where 3 and 4 came from in the array (1 and 3 aelemnts).

The VBA example is recorded by me when manually selecting. I did 2 clicks selecting with one click many values (there is a plus like in explorer tree) and one click can select many. Here is a small screenshot:

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.