Update the Excel UDF that comes with AutoIt

[water]

I would like for AutoFilter to allow for number filters, i.e., less than, greater than, etc.

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

Edited September 7, 2013 by water

[water]

I am trying to write a script to parse a fixed delimited file using the _Excel_BookOpenText.

What do you exactly mean by "fixed delimited"? Function _Excel_BookOpenText only supports CSV files (fields are separated by semi-colons, commas etc.)

A fixed length field oriented import function (e.g. Byte 1-5 = field 1, Byte 6-32 = field2 etc.) doesn't exist at the moment.

Can you post an example of the file you try to import?

[scbuckeye]

What do you exactly mean by "fixed delimited"? Function _Excel_BookOpenText only supports CSV files (fields are separated by semi-colons, commas etc.)

A fixed length field oriented import function (e.g. Byte 1-5 = field 1, Byte 6-32 = field2 etc.) doesn't exist at the moment.

Can you post an example of the file you try to import?

I guess that is the problem then.  It is a fixed width file, not a CSV.  It is is not separated by commas, semicolons, etc.  It does the import corrctly, but when I try to save it does not save as a workbook.  I guess this is since it was originally a text file.  Can't upload the file because of HIPAA regulations.

[water]

You could open a new empty Excel Workbook, start the macro recorder and then do the fixed file import.

If you post the resulting VBA code I could translate it to an AutoIt function.

[scbuckeye]

You could open a new empty Excel Workbook, start the macro recorder and then do the fixed file import.

If you post the resulting VBA code I could translate it to an AutoIt function.

Sub FixedDelimited()
'
' FixedDelimited Macro
'

'
    ChDir "C:\Documents and Settings\ahericks\My Documents\Dropbox\BHRR Reports"
    Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\ahericks\My Documents\Dropbox\BHRR Reports\BV_20130910.txt" _
        , Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 2), Array(20, 1), Array(28, 9), Array(29, 1), Array(37, 1), Array(45, 1), Array(59 _
        , 1), Array(77, 1), Array(89, 1), Array(104, 1), Array(122, 1)), TrailingMinusNumbers _
        :=True
End Sub

Here is the VBA code.  Thanks for your help. 

[water]

Could you please test this function? Parameter is is the Excel Application object as retzurned by _Excel_Open.

#include <ExcelConstants.au3>
Func _Excel_BookOpenTextFixed($oExcel)

    Local $aField1[2] = [0, 2]
    Local $aField2[2] = [20, 1]
    Local $aField3[2] = [28, 9]
    Local $aField4[2] = [29, 1]
    Local $aField5[2] = [37, 1]
    Local $aField6[2] = [45, 1]
    Local $aField7[2] = [59, 1]
    Local $aField8[2] = [77, 1]
    Local $aField9[2] = [89, 1]
    Local $aField10[2] = [104, 1]
    Local $aField11[2] = [122, 1]
    Local $aFieldInfo[11] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8, $aField9, $aField10, $aField11]
    $oExcel.Workbooks.OpenText("C:\Documents and Settings\ahericks\My Documents\Dropbox\BHRR Reports\BV_20130910.txt", _
            437, 1, $xlFixedWidth, Default, Default, Default, Default, Default, Default, Default, Default, _
            $aFieldInfo, Default, Default, Default, True)

EndFunc   ;==>_Excel_BookOpenTextFixed

[scbuckeye]

That works perfect!

Everything works great...just one issue with _Excel_RangeCopyPaste.  I cannot get only the values to paste to second workbook.  I believe I have the variables correct.  Here is the code:

expandcollapse popup

#;Set Include files for AutoIT
#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>
#include <GuiToolbar.au3>
#include <GuiListBox.au3>
#include <GuiConstantsEx.au3>
#include <DateTimeConstants.au3>
#include <ExcelConstants.au3>

Global $oExcel = _Excel_Open()

_Excel_BookOpenTextFixed($oExcel)
RangeCopyPaste($oExcel)

Func _Excel_BookOpenTextFixed($oExcel)

    Local $sTextFile = @UserProfileDir & "\My Documents\Dropbox\BHRR Reports\BV_" & @YEAR & @MON & @MDAY & ".txt"
    Local $sWorkbook = @UserProfileDir & "\My Documents\Dropbox\BHRR Reports\BV_" & @YEAR & @MON & @MDAY & ".xlsx"
    Local $aField1[2] = [0, 2]
    Local $aField2[2] = [20, 1]
    Local $aField3[2] = [28, 9]
    Local $aField4[2] = [29, 1]
    Local $aField5[2] = [37, 1]
    Local $aField6[2] = [45, 1]
    Local $aField7[2] = [59, 1]
    Local $aField8[2] = [77, 1]
    Local $aField9[2] = [89, 1]
    Local $aField10[2] = [104, 1]
    Local $aField11[2] = [122, 1]
    Local $aFieldInfo[11] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8, $aField9, $aField10, $aField11]
    $oExcel.Workbooks.OpenText($sTextFile, 437, 1, $xlFixedWidth, Default, Default, Default, Default, Default, Default, Default, Default, _
            $aFieldInfo, Default, Default, Default, True)
    $oExcel.ActiveWorkBook.SaveAs($sWorkbook, $xlWorkbookDefault)
    $oExcel.ActiveWorkBook.Close()

EndFunc   ;==>_Excel_BookOpenTextFixed

Func RangeCopyPaste($oExcel)

    Local $sWorkbook2 = @UserProfileDir & "\My Documents\Dropbox\My Dropbox Folder\MSM vs NRX.xlsx"
    Local $oWorkbook2 = _Excel_BookOpen($oExcel, $sWorkbook2)
    Local $sWorkbook1 = @UserProfileDir & "\My Documents\Dropbox\BHRR Reports\BV_" & @YEAR & @MON & @MDAY & ".xlsx"
    Local $oWorkbook1 = _Excel_BookOpen($oExcel, $sWorkbook1)
    Local $oRange = $oWorkbook1.Worksheets(1).Range("F26:I26")
    _Excel_RangeCopyPaste($oWorkbook2.Worksheets(1), $oRange, "F5:I5", Default, $xlPasteValues)

EndFunc   ;==>RangeCopyPaste

[scbuckeye]

Water,

Nevermind...I changed it to copy to the clipboard and then paste, and that corrected the issue.  Thanks so much for all your help!

[water]

Glad you could solve your problem

But I'm sure a direct copy from workbook to workbook is possible (at least that is what I had in mind when coding).

[water]

I have finished the filter functions (will be published with the next beta version).

Does anyone want to see grouping functions in the new UDF?

[FlashpointBlack]

This is a very nice UDF indeed. One thing I'm curious about that I didn't see mentioned anywhere... Would it be possible to add in support for adding an image to a spreadsheet? I've looked everywhere and I've had to ghetto it out using simulated keypresses and such that I'd much rather stay away from. Aside from being error-prone, it's also quite slow.

I figure you'd be the guy to ask!

Thanks for this Water, it's really nice work!

[DW1]

This is a very nice UDF indeed. One thing I'm curious about that I didn't see mentioned anywhere... Would it be possible to add in support for adding an image to a spreadsheet? I've looked everywhere and I've had to ghetto it out using simulated keypresses and such that I'd much rather stay away from. Aside from being error-prone, it's also quite slow.

I figure you'd be the guy to ask!

Thanks for this Water, it's really nice work!

No error checking at all, and Water may very well already have something for this that I am not aware of.

#include <Excel.au3>

Local $oExcel = _ExcelBookNew()

_ExcelInsertPicture($oExcel, "C:\Users\DW\Pictures\_MyPC.jpg", "C6")

Func _ExcelInsertPicture(ByRef $oExcel, $sFile, $sRange = -1)
    If $sRange <> -1 Then $oExcel.Range($sRange).Select
    $oExcel.ActiveSheet.Pictures.Insert($sFile)
EndFunc

[water]

Would it be possible to add in support for adding an image to a spreadsheet?

Welcome to AutoIt and the forum!

I will have a look at a function to add (inline) pictures, shapes etc. to a worksheet.

[DW1]

EDIT: See >below post for updated example based on Excel Rewrite.au3

Edited September 14, 2013 by danwilli

[water]

Thanks for this function!

I will have a look as soon as I find some spare time. I then will have to do some reading to see how many methods Excel provides to work with pictures and/or shapes.

Stay tuned.

[water]

danwilli,

do you know where on MSDN I can find the docu for the "Pictures" collection?

As it is a collection for the worksheet object I would have expected it here. But nope.

[DW1]

danwilli,

do you know where on MSDN I can find the docu for the "Pictures" collection?

As it is a collection for the worksheet object I would have expected it here. But nope.

I did some research here.  The pictures collection should no longer be used, which is why it isn't listed.  If you use the pictures collection to insert a picture in an excel file, it will simply be a link to the picture and not be included in the file.

What should be used is the Shapes Object (Shapes.AddPicture method).

I have written function _ExcelAddPicture() based on this in the format of Excel Rewrite.au3

expandcollapse popup

#include <Excel Rewrite.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
Local $sPicFile = "C:\Users\DW\Pictures\_MyPC.jpg"

$oPic1 = _ExcelAddPicture($oWorkbook, Default, $sPicFile)
$oPic2 = _ExcelAddPicture($oWorkbook, Default, $sPicFile, "C6:F15")
$oPic3 = _ExcelAddPicture($oWorkbook, Default, $sPicFile, "G6:J15", Default, Default, False)
$oPic4 = _ExcelAddPicture($oWorkbook, Default, $sPicFile, "H18", Default, 200)


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelAddPicture
; Description ...: Add a picture on the specified workbook and worksheet.
; Syntax.........: _ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, [$vRange = Default, [$iWidth = Default, [$iHeight = Default, [$bScale = True]]]])
; Parameters ....: $oWorkbook  - Excel workbook object
;                  $vWorksheet - Name, index or worksheet object to be written to. If set to keyword Default the active sheet will be used
;                  $sFile      - Full path to picture file being added
;                  $vRange     - Optional: Either an A1 range or a range object - If multi-cell range $iWidth and $iHeight will be ignored and will use the range width/height (default = "A1")
;                                See the Remarks section for details
;                  $iWidth     - Optional: If specified, sets the width of the picture. If not specified, width will adjust automatically. (default = Automatic)
;                                See the Remarks section for details
;                  $iHeight    - Optional: If specified, sets the height of the picture. If not specified, height will adjust automatically. (default = Automatic)
;                                See the Remarks section for details
;                  $bScale     - Only used if $vRange is a multi-cell range.
;                                |True will maintain image aspect ratio while staying within the bounds of $vRange.
;                                |False will fill the $vRange regardless of original aspect ratio.
;                                |(default = True).
; Return values .: Success - Returns a Shape object that represents the new picture
;                  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 occurred when adding picture. @extended is set to the COM error code
;                  |5 - $sFile does not exist
; Author ........: DanWilli based on work by water
; Modified.......:
; Remarks .......: If $vRange is multi cell $iWidth and $iHeight will be ignored (to specify width/height not based on range width/height, specify a single cell $vRange)
;                  |
;                  If only one of $iWidth and $iHeight is specified, the other (set to default) will be scaled to maintain the original aspect ratio of the picture.
;                  If both $iWidth and $iHeight are specified, the picture will use the specified values regardless of original aspect ratio of the picture.
;                  If neither $iWidth nor $iHeight are specified, the picture will be auto sized to the size of the original picture.
;                  |
;                  $bScale will be ignored unless a multi cell range is specified (see Parameters for details)
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, $vRange = Default, $iWidth = Default, $iHeight = Default, $bScale = True)
    Local $Return
    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 = "A1"
    If Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    If Not FileExists($sFile) Then Return SetError(5, 0, 0)
    If $vRange.Columns.Count = 1 And $vRange.Rows.Count = 1 Then
        If $iWidth = Default And $iHeight = Default Then
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, 0, 0)
            If @error Then Return SetError(4, @error, 0)
            $Return.Scalewidth(1, -1, 0)
            $Return.Scaleheight(1, -1, 0)
        ElseIf $iWidth = Default Then
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, 0, 0)
            If @error Then Return SetError(4, @error, 0)
            $Return.Visible = 0
            $Return.Scalewidth(1, -1, 0)
            $Return.Scaleheight(1, -1, 0)
            $Return.Scalewidth($iHeight / $Return.Height, -1, 0)
            $Return.Scaleheight($iHeight / $Return.Height, -1, 0)
            $Return.Visible = 1
        ElseIf $iHeight = Default Then
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, 0, 0)
            If @error Then Return SetError(4, @error, 0)
            $Return.Visible = 0
            $Return.Scalewidth(1, -1, 0)
            $Return.Scaleheight(1, -1, 0)
            $Return.Scaleheight($iWidth / $Return.Width, -1, 0)
            $Return.Scalewidth($iWidth / $Return.Width, -1, 0)
            $Return.Visible = 1
        Else
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, $iWidth, $iHeight)
            If @error Then Return SetError(4, @error, 0)
        EndIf
    Else
        If $bScale = True Then
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, 0, 0)
            If @error Then Return SetError(4, @error, 0)
            $Return.Visible = 0
            $Return.Scalewidth(1, -1, 0)
            $Return.Scaleheight(1, -1, 0)
            Local $iRw = $vRange.Width / $Return.Width
            Local $iRh = $vRange.Height / $Return.Height
            If $iRw < $iRh Then
                $Return.Scaleheight($iRw, -1, 0)
                $Return.Scalewidth($iRw, -1, 0)
            Else
                $Return.Scaleheight($iRh, -1, 0)
                $Return.Scalewidth($iRh, -1, 0)
            EndIf
            $Return.Visible = 1
        Else
            $Return = $vWorksheet.Shapes.AddPicture($sFile, -1, -1, $vRange.Left, $vRange.Top, $vRange.Width, $vRange.Height)
            If @error Then Return SetError(4, @error, 0)
        EndIf
    EndIf
    Return $Return
EndFunc   ;==>_ExcelAddPicture

The above function returns a shape object, and I assume other functions will eventually be doing the same in the Excel rewrite.  If other functions will also be returning the shape object, it would be nice to have an included function for setting Z-order, rotation, placement, etc for shape objects, maybe like a ShapeSetProperties function or something similar.

Thoughts?

Edited September 14, 2013 by danwilli

[water]

Thanks a lot for the explanation and the wonderful function!

I will test as soon as I get my hands on a PC with Excel installed.

I will do some reading about shapes and see if we can design some general functions to handle all kinds of shapes.

[water]

I just tested the function and it really looks great!

Do you think its sensible to let the user specify the top/left location of the picture, not just the upper/left cell?

I'm thinking of changing the function call from

_ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, [$vRange = Default, [$iWidth = Default, [$iHeight = Default, [$bScale = True]]]])

to

_ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, [$vRangeOrLeft = Default[ $iTop = Default[, [$iWidth = Default, [$iHeight = Default, [$bScale = True]]]]])

If $vRangeOrLeft is a range then the function works as it does now and ignores $iTop. If it is a number then it is interpreted as the left and $iTop as the top coordinates of the picture.

[DW1]

I just tested the function and it really looks great!

Do you think its sensible to let the user specify the top/left location of the picture, not just the upper/left cell?

I'm thinking of changing the function call from

_ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, [$vRange = Default, [$iWidth = Default, [$iHeight = Default, [$bScale = True]]]])

to

_ExcelAddPicture($oWorkbook, $vWorksheet, $sFile, [$vRangeOrLeft = Default[ $iTop = Default[, [$iWidth = Default, [$iHeight = Default, [$bScale = True]]]]])

If $vRangeOrLeft is a range then the function works as it does now and ignores $iTop. If it is a number then it is interpreted as the left and $iTop as the top coordinates of the picture.

Great idea Water, I'd agree with that change.  If there are any other changes in either the description or the function to make this fit the rest of the excel rewrite properly, I trust you to make any of those decisions and changes.