Sign in to follow this  
Followers 0
ericbailey2

Excel Help Again

9 posts in this topic

Awhile back I had assistance with a script using the ExcelCOM_UDF

Basically it opens up a specified excel sheet, selects a specific row, then applies the autofilter to that row.

I was wondering if it would be possible to expand this further and have it filter for certain criteria.

For example, in column 3 there is a heading called Cities. How can I filter to only display "Los Angeles" or something of the like?

CODE
; Open the workbook, select first row, apply AutoFilter

$oExcel = ObjCreate("Excel.Application")

With $oExcel

.Visible = True ; False if you don't want Excel visible

.WorkBooks.Open("C:\File.XLS")

.ActiveWorkbook.Sheets(1).Rows(4).Select()

.Selection.AutoFilter; Once, to set the filter

EndWith

Share this post


Link to post
Share on other sites



Most likely something like

Selection.AutoFilter(Field:=3, Criteria1:="los angeles")

I don't know if that is one or two string parameters being passed, but I think it would either be:

Selection.AutoFilter('Field:=3, Criteria1:="los angeles"') ; one parameter string

-- or --

Selection.AutoFilter('Field:=3', 'Criteria1:="los angeles"') ; two parameter strings

:D


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

I don't know if that is one or two string parameters being passed, but I think it would either be:

Selection.AutoFilter('Field:=3, Criteria1:="los angeles"') ; one parameter string

-- or --

Selection.AutoFilter('Field:=3', 'Criteria1:="los angeles"') ; two parameter strings

:D

I tried both, its giving me the following error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Script.au3"

C:\Script.au3 (9) : ==> The requested action with this object has failed.:

.Selection.AutoFilter('Field:=3', 'Criteria1:="Los Angeles"')

.Selection.AutoFilter('Field:=3', 'Criteria1:="Los Angeles"')^ ERROR

>Exit code: 1 Time: 0.917

I even tried changing a few things around. Still no go.

Share this post


Link to post
Share on other sites

I tried a few other things, I believe their is a way, I just think that the syntax is wrong.

Share this post


Link to post
Share on other sites

Try this:

Selection.AutoFilter(3, "los angeles")

Share this post


Link to post
Share on other sites

I think were getting closer, this time it ran without giving me an error, but it didn't filter.

Share this post


Link to post
Share on other sites

Nevermind,

I had to add an extra .Selection.AutoFilter

line above the one you gave me. Thank you very much :D

Share this post


Link to post
Share on other sites

You're welcome, glad I was able to help.

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