Update the Excel UDF that comes with AutoIt

[scbuckeye]

 

Here is a test example. What do you think?

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel Rewrite.au3>
#include <Constants.au3>

; Create application object and open an example workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\_Excel1.xls")
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example", "Error opening workbook '_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

Example1($oWorkbook)

Exit

; *****************************************************************************
; Example 1
; *****************************************************************************
Func Example1($oWorkbook)

    ; Filter the complete active worksheet on column 1. Only show rows >20 and <40
    _Excel_RangeFilter($oWorkbook, Default, Default, 1, ">20", 1, "<40")
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox(0, "", "Filtered on column 1. Only show rows >20 and <40")

    ; Add a filter to column 2. Only show rows <310
    _Excel_RangeFilter($oWorkbook, Default, Default, 2, "<310")
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox(0, "", "Added filter on column 2. Only show rows <310")

    ; Remove the filter from column 1
    _Excel_RangeFilter($oWorkbook, Default, Default, 1)
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox(0, "", "Removed filter from column 1.")

    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFilter Example 1", "Data successfully filtered")

EndFunc   ;==>Example1

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeFilter
; Description ...: Sets/unsets a filter definition and filters the range.
; Syntax.........: _Excel_RangeFilter($oWorkbook, $vWorksheet, $vRange = Default, $iField[, $sCriteria1 = Default[, $sOperator = Default[, $sCriteria2 = Default]]])
; Parameters ....: $oWorkbook  - Excel workbook object
;                  $vWorksheet - Name, index or worksheet object to be filtered. If set to keyword Default the active sheet will be filtered
;                  $vRange     - A range object, an A1 range or keyword Default to filter all cells in the specified worksheet
;                  $iField     - Integer offset of the field on which you want to base the filter (the leftmost field is field one)
;                  $sCriteria1 - The criteria (a string; for example "MS" or ">40"). Use "=" to find blank fields, or use "<>" to find nonblank fields.
;                  |             If this argument is omitted, the criteria is All.
;                  |             If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, "10")
;                  $sOperator  - One of the constants of the XlAutoFilterOperator enumeration specifying the type of filter
;                  $sCriteria2 - The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria
; Return values .: Success - Returns 1
;                  Failure - Returns 0 and sets @error:
;                  |1 - $oWorkbook is not an object or not a workbook object
;                  |2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code
;                  |3 - $vRange is invalid. @extended is set to the COM error code
;                  |4 - Error returned by the Filter method. @extended is set to the COM error code
; Author ........: water
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeFilter($oWorkbook, $vWorksheet, $vRange, $iField, $sCriteria1 = Default, $sOperator = Default, $sCriteria2 = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If Not IsObj($vWorksheet) Then
        If $vWorksheet = Default Then
            $vWorksheet = $oWorkbook.ActiveSheet
        Else
            $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
        EndIf
        If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If $vRange = Default Then
        $vRange = $vWorksheet.Usedrange
    ElseIf Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    $vRange.AutoFilter($iField, $sCriteria1, $sOperator, $sCriteria2)
    If @error Then Return SetError(4, @error, 0)
    ; If no filters remain then AutoFiltermode is set off
    If $vWorksheet.Filtermode = False Then $vWorksheet.AutoFiltermode = False
    Return 1
EndFunc   ;==>_Excel_RangeFilter

 

Those are exactly what I was looking for.  What about filtering more than two criteria?  i.e. Select 4 different items from the same column.

[water]

Set $sOperator to $xlFilterValues and set $sCriteria1 to an array containing all the values you want to filter.

[scbuckeye]

Set $sOperator to $xlFilterValues and set $sCriteria1 to an array containing all the values you want to filter.

Okay, thanks.  I'll try that out.

[water]

The next beta will include an example to do just that.

[scbuckeye]

Okay, thanks.  I'll try that out.

Worked perfect.  Not to be a pain, but what if I wanted to filter everything, but 4 items?  I need to delete all rows that do not have one of four values in a certain cloumn.  Is there a way to do this with _Excel_RangeFind and then _Excel_RangeDelete, or should I stick with the filter?

[water]

How would you do it manually?

[scbuckeye]

How would you do it manually?

 

Uncheck those items in the filter, then delete all rows.  Then I unaplly the filters.  The reason for needing to do it this way, is I need to create a pivot table afterwards.

Edited September 18, 2013 by scbuckeye

[scbuckeye]

I tried doing the filter of the array with the "<>" but that did not work.

[water]

I would do it with _Excel_RangeFind. The returned array holds the cells address in element 2.

Example:

#include <Excel Rewrite.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
Global $aResult = _Excel_RangeFind($oWorkbook, "This is a Story that's even longer")
$oRange = $oWorkbook.Activesheet.Range($aResult[0][2])
$oRange.EntireRow.Delete

Edited September 19, 2013 by water

[FlashpointBlack]

Wow, great! that actually really helps alot! I apologize for the late reply. It isn't because I'm unthankful, but because I expected further replies to this thread to show up in my "new posts". fantastic, I really appreciate it! keep up the great work!

[water]

Glad you like the UDF

[water]

danwilli,

here is the slightly modified function that now allows to position the picture anywhere on the worksheet. All other parameters are handled as if the user had specified a single cell for $vRangeOrLeft.

You need to create a "_Excel.jpg" file in the directory where the script is stored for the example to work properly.

What do you think?

_Excel_PictureAdd.au3

[DW1]

danwilli,

here is the slightly modified function that now allows to position the picture anywhere on the worksheet. All other parameters are handled as if the user had specified a single cell for $vRangeOrLeft.

You need to create a "_Excel.jpg" file in the directory where the script is stored for the example to work properly.

What do you think?

Looks great.  Maybe add one more example showing a range filled completely with $bScale = False?

[water]

Thanks for the reply!

I will extend the example.

[water]

Released Beta 2.

For download and a history of changes please see post #1.

[Tjalve]

Great work so far!

im trying to use the sort function but i keep getting:

C:Program Files (x86)AutoIt3IncludeExcel Rewrite.au3(168,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

[water]

You need to run the examples with the latest AutoIt beta version.

[Tjalve]

You need to run the examples with the latest AutoIt beta version

I am. I downloaded it and installed again just to be 100% sure.

[water]

Can you please post the output from the SciTE output pane?

The message tells me that you still run the script with version < 3.3.9.x

[Tjalve]

Can you please post the output from the SciTE output pane?

The message tells me that you still run the script with version < 3.3.9.x

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UsersAdministratorSkyDriveFrån PrimescriptNHresultatread_all_results_for_one_drive.au3" /UserParams    

+>21:04:25 Starting AutoIt3Wrapper v.2.1.2.9    Environment(Language:0409  Keyboard:0000041D  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)

>Running AU3Check (1.54.22.0)  from:C:Program Files (x86)AutoIt3

C:\Program Files (x86)\AutoIt3\Include\Excel Rewrite.au3(168,56) : ERROR: ObjGet() [built-in] called with wrong number of args.

        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

I was under the impression that 3.3.9.21 is the latest beta? Thats what im running. In you initial post, it say that you need 3.3.9.2 or later?