Excel UDF

From AutoIt Wiki
Jump to navigation Jump to search

The Excel UDF offers functions to control and manipulate Microsoft Excel workbooks.
This page describes the Excel UDF that comes with AutoIt 3.3.12.0 or later.

Features

New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. The new extensions (e.g. xlsx) were not (fully) supported, new functions were missing etc. The current version of the Excel UDF lifts this limitations.

  • Works with as many instances of Excel as you like - not just one
  • Works with any Workbook - not just the active one
  • Works with any Worksheet - not just the active one
  • Only does what you tell it to do - no implicit "actions"
  • Only one function to read from a cell or a range
  • Only one function to write a string, a 1D or 2D array to a cell or a range
  • Support for every file format Excel supports
  • Speed enhancements when transferring data from/to an Excel sheet (20 - 100 times faster)

Concepts

Area

The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection.

Range

A Range is a rectangular block made of one or more cells that Excel treats as a unit. The functions of the UDF mainly work with ranges. A range - unlike a selection - is not visible on the screen.
Examples how to define special ranges can be found here.

Cell reference

The UDF only supports the A1 form (example: "B7") to reference cells. The R1C1 form (example "R7C2") is not supported. But the UDF provides functions to translate a cell reference between this two forms.
You can reference cells by name as well.

Examples

  • Single cell: "B7"
  • Multiple cells: "A1:B7"
  • Row(s): "2" or "3:5"
  • Column(s): "B" or "D:F"
  • Name: "TestRange"

Filter

The Excel Autofilter allows you to view specific rows in an Excel spreadsheet, while hiding the other rows in the worksheet based on various criteria.
Details and example scripts how to work with filters can be found here.

Date and Time

Excel stores date and time as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt .
This is called serial date-time.

When you enter a time without a date value (e.g. 15:00) the date portion is a zero. This indicates that there is no date associated with the time and does not automatically put in the current date.

Date

The integer portion of the number represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544.
Actually, this number is one greater than the actual number of days. Why can be found here.

Time

The fractional portion of the number represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25,

Example script (written by AutoBert) to convert serial date-time to a readable format: 

#include <Date.au3>
$nExelDateTime = 42567.25
ConsoleWrite(_ConvertSerialDateTime($nExelDateTime) & @CRLF)

Func _ConvertSerialDateTime($nDT)
    Local Const $dtExcel = '1899/12/31 00:00:00'
    Local $iDate = Int($nDT) - 1 ; Adjusted after reading wiki and why result is 1 day to much: http://www.cpearson.com/excel/datetime.htm
    Local $iTime = Mod($nDT, 1)
    $iTime = Int(24 * 3600 * $iTime)
    $dtRes = _DateAdd('D', $iDate, $dtExcel)
    $dtRes = _DateAdd('s', $iTime, $dtRes)
    Return $dtRes
EndFunc

Features not covered by the UDF

The UDF only covers basic user needs. Single line functions (like switching to another sheet) or functions with too many parameters (like formatting a cell or range) are not covered by this UDF. You need to use the Excel COM yourself.
I will give a few examples here. The rest can be found on MSDN.

Format a range

Alignment

; Horizontal alignment
; Enumeration for Excel 2010: http://msdn.microsoft.com/en-us/library/ff840772%28v=office.14%29.aspx
$oRange.HorizontalAlignment = $XlHAlign ; Can be xlCenter, xlDistributed, xlJustify, xlLeft or xlRight of the XlHAlign enumeration.

; Vertical alignment
; Enumeration for Excel 2010: http://msdn.microsoft.com/en-us/library/ff835305%28v=office.14%29.aspx
$oRange.VerticalAlignment = $XlVAlign ; Can be xlBottom, xlCenter, xlDistributed, xlJustify or xlTop of the XlVAlign enumeration.

Background

Sets the background to yellow: 

With $oWorkbook.Sheets(1).Range("B2").Interior.ColorIndex = 6 ; Index value into the current color palette, or as one of the XlColorIndex constants.

Borders

Global $xlEdgeBottom = 9 ; XlBordersIndex enumeration. Border at the bottom of the range.
Global $xlContinuous = 1 ; XlLineStyle Enumeration. Continuous line.
Global $xlThin = 2 ; XlBorderWeight Enumeration. Continuous line. Thin line.
With $oWorkbook.Sheets(1).Range("B2").Borders($xlEdgeBottom)
    .LineStyle = $xlContinuous
    .Weight = $xlThin
    .ColorIndex = 3 ; Index value into the current color palette, or as one of the XlColorIndex constants.
EndWith

Font

Bold etc.

True if the font is bold. Read/write. 

$oRange.Font.Bold = True

This works similar for Italic, Strikethrough, Subscript, Superscript and Underline.

Color, ColorIndex

Color: Returns or sets the primary color of the object. Use the RGB calculation below to create a color value.
ColorIndex: Returns or sets a variant value that represents the color of the font. The color is specified as an index value into the current color palette.

Object Color
Border The color of the border.
Borders The color of all four borders of a range. If they're not all the same color, Color returns 0 (zero).
Font The color of the font.
Interior The cell shading color or the drawing object fill color.
Tab The color of the tab. 
$iRed = 234 ; Hex 0xEA
$iGreen = 163 ; Hex 0xA3
$iBlue = 22 ; Hex 0x16
$oRange.Font.Color = $iRed + ($iGreen * 256) + ($iBlue * 256 * 256) ; Set the color of the font to some kind of orange. Or written as 0xBGR: 0x16A3EA
$oRange.Borders.ColorIndex = 5 ; Set the color of all four borders to blue
Name

Returns or sets a variant value that represents the name of the font. 

$oRange.Font.Name = "Arial"
Size

Returns or sets the size of the font specified in units of points. 

$oRange.Font.Size = 12
Underline

Returns or sets the type of underline applied to the font. Can be one of the XlUnderlineStyle constants xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting or xlUnderlineStyleDoubleAccounting. Read/write. 

$oRange.Font.Underline = $xlUnderlineStyleSingle

Height/Width

Property Description
ColumnWidth Returns or sets the width of all columns in the specified range. If columns in the range have different widths, this property returns null.

One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

RowHeight Returns or sets the height of all rows in the specified range, measured in points. Returns null if the rows in the specified range aren’t all the same height.
Method Description
AutoFit Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit. 
$oRange.ColumnWidth = 20 ; Set all columns of the range to a width of 20 characters.
$oWorkbook.Sheets("Sheet1").Range("A1:E1").Columns.AutoFit ; Set the width of columns A through E on Sheet1 to achieve the best fit, based only on the contents of cells A1:E1.

Number, Date/Time Format

Returns or sets a variant value that represents the format code for the object.
This property returns Null if all cells in the specified range don't have the same number format.
The format code is the same string as the Format Codes option in the Format Cells dialog box. The Format function uses different format code strings than do the NumberFormat and NumberFormatLocal properties.
A number format consists of up to 4 items, separated by semicolons. Each of the items is an individual number format. The first by default applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text.
A very good description of format codes for numbers, date/time etc. can be found here.

Format code Description
@ Format as string
General Default format
0000 Format number with 4 digits, add leading zeroes where needed
[Blue]$#,##0;[Red]$#,##0;$#,##0 Format positive numbers in blue, negative in red and 0 in default color (black). The numbers are prefixed with the dollar sign and a thousands separator is inserted. The numbers are displayed as integers.

Available colors are [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], and [Yellow].

[Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0 Format values >= 3000 in blue, values < 0 in read and all other numbers in default color (black).
0" feet" append a label to the number 
$oRange.NumberFormat = "General"
$oRange.NumberFormat = "dddd, mmmm dd, yyyy" ; Returns Sunday, July 04, 2004 for date 7/4/2004

Format a window

Freeze panes

Freezes the first column and first row: 

$oWorkbook.Activesheet.Range("B2").Select
$oExcel.ActiveWindow.FreezePanes = True

Script breaking changes after AutoIt version 3.3.10.2

New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. New file types and new functions needed to be supported, hence the UDF was complete rewritten.

Some functions/parameters have been removed or renamed, new functions/parameters have been added. A detailed list of changes can be found here.

General

All function names have been changed from _Excel* to _Excel_*.

@extended no longer contains the number of the invalid parameter. The code returned in @error tells exactly what went wrong.

The following list shows the old/new function/parameter name (a "-" is shown if the function/parameter has been removed) and some example scripts how to mimic the behaviour of the "old" UDF. If there is no entry for a removed function/parameter then there is no need for this functionality.

Function -/_Excel_Open

It's mandatory now to call function _Excel_Open before any other function (except _Excel_BookAttach). This function didn't exist in the old UDF. @extended is set if Excel was already running.

Function _ExcelFontSetProperties/-

There are so many formatting functions in Excel that they can't be covered by a few functions. The function only contained a single line of code. So it was removed. Use the code examples above to format a range.

Function _ExcelHorizontalAlignSet/-

There are so many formatting functions in Excel that they can't be covered by a few functions. The function only contained a single line of code. So it was removed. Use the code examples above to format a range.

Function _ExcelSheetActivate/-

The function only contained a single line of code. So it was removed. Replace the function with the following code: 

$oWorkbook.Sheets(x).Activate ; x can be the number or name of the sheet to be activated

Function _ExcelSheetNameGet/-

The function only contained a single line of code. So it was removed. Replace the function with one of the following lines of code: 

$sSheetName = $oSheet.Name ; $oSheet is the object of the worksheet 
$sSheetName = $oWorkbook.Sheets(n).Name ; n is the index of the worksheet starting from 1

Function _ExcelSheetNameSet/-

The function only contained a single line of code. So it was removed. Replace the function with one of the following lines of code: 

$oSheet.Name = "New name of the sheet" ; $oSheet is the object of the worksheet
$oWorkbook.Sheets(n).Name = "New name of the sheet" ; n is either the index or the current name of the worksheet

Compare example scripts

In this section I will show how some selected example scripts taken from AutoIt 3.3.8.1 should look like with the new Excel UDF.
To enhance readability error checking statements have been omitted.
But I highly recommend to check for errors after each call of a _Excel_* function.

AutoIt 3.3.8.1 AutoIt 3.3.12.0 and later
_ExcelBookAttach _Excel_BookAttach 
#include <Excel.au3>
Local $sFilePath = @TempDir & "\Temp.xls"
_ExcelBookOpen($sFilePath)
Local $oExcel = _ExcelBookAttach($sFilePath)

#include <Excel.au3>
Local $sWorkbook = @TempDir & "\Temp.xls"
; The workbook you want to connect to must already
; have been opened by the user or another script
$oWorkbook = _Excel_BookAttach($sWorkbook)
_ExcelBookClose _Excel_BookClose 
#include <Excel.au3>
Local $oExcel = _ExcelBookNew()
_ExcelBookClose($oExcel)

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
_Excel_BookClose($oWorkbook, False)
_ExcelBookNew _Excel_BookNew 
#include <Excel.au3>
Local $oExcel = _ExcelBookNew()

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel, 2)
_ExcelBookOpen _Excel_BookOpen 
#include <Excel.au3>
Local $sFilePath1 = @ScriptDir & "\Test.xls"
Local $oExcel = _ExcelBookOpen($sFilePath1)

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)

Miscellaneous

Transpose limits

Functions _Excel_RangeWrite, _Excel_RangeRead and _Excel_RangeCopyPaste use the Excel transpose method. This method has an undocumented limit based on the Excel version you run. Details about Excel up to version 2000 can be found here:

Version Format Max. rows Max. columns Transpose limit
Excel 2016 (16.0) XLSX 1.048.576 16.384 (XFD) 65536 rows (according to the newtonexcelbach article above)
Excel 2013 (15.0) XLSX 1.048.576 16.384 (XFD) 65536 rows (according to the newtonexcelbach article above)
Excel 2010 (14.0) XLSX 1.048.576 16.384 (XFD) 65536 rows (tested by myself with an [65537][1] array)
Excel 2007 (12.0) XLSX 1.048.576 16.384 (XFD) (because Excel 2002 and Excel 2010 have the same limit)
Excel 2003 (11.0) XLS 65.536 256 (IV) (because Excel 2002 and Excel 2010 have the same limit)
Excel 2002 (10.0) XLS 65.536 256 (IV) 65536 rows (tested by kylomas with an [65537][2] array)
Excel 2000 (9.0) XLS 65.536 256 (IV) 5461 cells
Excel 97 (8.0) XLS 65.536 256 (IV) 5461 cells
Excel 95 (7.0) XLS 16.384 256 (IV) 5461 cells
Excel 5.0 (1993) XLS 16.384 256 (IV)
Excel 4.0 (1992) XLS 16.384 256 (IV)
Excel 3.0 (1990) XLS 16.384 256 (IV)
Excel 2.0 (1987) XLS 16.384 256 (IV)
Excel 1.0 (1985) XLS 16.384 256 (IV)

Furthermore the transpose method has a cell size limit of 255 chracters.

Depending on the Excel version you get or do not get an error for both limitations. When the transpose method fails use the internal _ArrayTranspose method by setting parameter $bForceFunc to True for functions _Excel_RangeRead and _ExcelRangeWrite. The function is limited by AutoIt's array limits only.

Script crashes

When your script processes a large amount of workbooks in a loop and suddenly crashes without error then try to run your script in 64 bit mode by adding 

#AutoIt3Wrapper_UseX64=y

to your script.

_Excel_RangeWrite writes no data

_Excel_RangeWrite does not write any data to a worksheet AND does not set @error. This can be caused by:

  • a cell in the data to be written exceeds the 255 characters limit
  • a cell containing data of type object
  • a cell containing the keyword Null

You could use something like this to check the correctness of your data. Row, Column and explanation get written to the console.
Example for a 2D array: 

#include <String.au3>
#include <AutoItConstants.au3>

; Create an array with invalid data
Global $aData[3][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)], [Null]]
Global $bDetail = True  ; If set to True each cell with invalid data is listed with zero based row, column and explanation
Global $bFix = 15       ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data
                        ; Can be a combination of any of the following values:
                        ; 1 - Set all objects to ""
                        ; 2 - Strip all cells with more than 32767 characters to 32767 characters using StringLeft
                        ; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft
						; 8 - Replace keyword Null with space
Global $aCount[] = [0, 0, 0, 0]
For $i = 0 To UBound($aData, 1) - 1
    For $j = 0 To UBound($aData, 2) - 1
        Select
            Case IsObj($aData[$i][$j])
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsObj" & @CRLF)
                If BitAND($bFix, 1) = 1 Then $aData[$i][$j] = ""
                $aCount[0] += 1
            Case StringLen($aData[$i][$j]) > 32767
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 32767" & @CRLF)
                If BitAND($bFix, 2) = 2 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 32767)
                $aCount[1] += 1
            Case StringLen($aData[$i][$j]) > 255
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 255" & @CRLF)
                If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)
                $aCount[2] += 1
			Case IsKeyword($aData[$i][$j]) = $KEYWORD_NULL
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsNull" & @CRLF)
                If BitAND($bFix, 8) = 8 Then $aData[$i][$j] = ""
                $aCount[3] += 1
        EndSelect
    Next
Next
ConsoleWrite(@CRLF & "SUMMARY" & @CRLF)
ConsoleWrite("Cells containing data type object        : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)
ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)
ConsoleWrite("Cells containing data > 255 characters   : " & $aCount[2] & ". Fixed: " & (BitAND($bFix, 4) = 4))
If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")
ConsoleWrite(@CRLF)
ConsoleWrite("Cells containing data type Null          : " & $aCount[3] & ". Fixed: " & (BitAND($bFix, 8) = 8) & @CRLF)

Check if workbook is password protected

There seems to be no way to check if a workbook is password protected without opening it.

When you call _Excel_BookOpen without providing a password, a window pops up asking for the password if it is protected. This window can't be automated.

Solution:

Call _Excel_BookOpen and provide a password (even a wrong one). If the workbook has no password on it, it will open with @error set to 0; if it does have a password it will not get opened and set @error to 3 and @extended to -2147352567.

Example as described by JLogan3o13 in this thread. 

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\test.xlsx", True, True, "abc", "abc")
If @error Then
    If @extended = -2147352567 Then
        ; Workbook is password protected
    Else
        ; Handle other @error such as missing file
    EndIf
Else
    ; File not password protected, do whatever
EndIf

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
Retrieved from "https://www.autoitscript.com/w/index.php?title=Excel_UDF&oldid=14893"