Jump to content

Excel Help Again


Recommended Posts

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

Link to comment
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
Link to comment
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.

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...