Yet Another -- ExcelCOM UDF

[Locodarwin]

I've taken on the task of writing a more formal and community-standards based ExcelCOM UDF, with the idea that I'd eventually propose it for inclusion with the extensive UDF set AutoIt installs with now.

This set of routines differs from Randallc's in a few important ways:

1. Instead of opening and closing the document for each call, my routines offer more flexibility. Basically, you call a function to open/create the workbooks, call others to write/read/format the workbook by active sheet, and call yet another to save/close out the workbook and terminate the object.

2. Each function is (or will be) written according to the UDF standards previously established by the community.

3. I will be creating different UDFs for different automation tasks in Excel for brevity's sake - namely, I'll be separating "common" functions from chart-related functions and other more specialized tasks. I'm thinking one UDF for worksheet-related tasks (the "common" set), one for charts, one for perhaps pivot tables, and others for maybe diagrams/drawing, lists, OLE, XML, and other not-as-common tasks. I suppose it depends on the size of finished UDFs, as yet undetermined. Right now I figure I'm about 90% complete on the common UDF, and about 30% complete on the chart UDF.

Suggestions, comments, contributions, and questions welcomed.

CHANGELOG

expandcollapse popup

---------01/05/08---------- v1.4




--------New functions:

_ExcelAttach() - Attach to an already running instance of Excel (contributed by big_daddy)

_ExcelPrintRange() - Prints a range of cells in the active worksheet

_ExcelPrintSheet() - Prints the specified worksheet

_ExcelBookPropertiesGet() and _ExcelBookPropertiesSet(), get or set some important book properties

_ExcelCreateBorders() is back and better than before

_ExcelReadSheetToArray() - Read the contents of a sheet (in whole or part) into a 2d array (contributed by PsaltyDS)

_ExcelWriteSheetFromArray() - Write the contents of a 2d array into a worksheet (contributed by PsaltyDS)




--------Updated functions:

_ExcelBookOpen() supports read-only, document open/read password, and document write-protect password

_ExcelBookSaveAs() supports document open/read password, document read/write password, document sharing, and sharing conflict resolution

_ExcelFindInRange() fixed and improved RegExp

_ExcelSheetUsedRangeGet() fixed and improved RegExp




--------Bug fixes:

* Typos in _ExcelHyperlinkInsert()

* _ExcelWriteFormula() had some R1C1 reference issues

* When using Opt("MustDeclareVars",1) the _ExcelOpen* commands toss out an undeclared variable error for the $oExcel variable. Fixed (thanks, Blue Drache).

* In the description of _ExcelBookSaveAs, the syntax shows "_ExcelSaveDocAs." Fixed (thanks, ChrisJakarta).

* Some typos corrected and error checking added to _ExcelCellMerge()







---------11/30/06---------- v1.32

Bug fixed for _ExcelSheetUsedRangeGet()




---------11/30/06---------- v1.31

Fixed typos in _ExcelCellFormat()




---------11/24/06---------- v1.3

v1.3

Changed: Added more extensive error handling to _ExcelSheetMove(), and gave it the ability to place a sheet after another, as well as before

Changed: Fixed and cleaned up _ExcelWriteArray()

Changed: Added read-only flag to _ExcelBookOpen()




Fixed: _ExcelHyperlinkInsert(), was very buggy




Added: _ExcelFindInRange(), new function that returns addresses of all instances of a search string in a range as a 2 dimensional array

Added: _ExcelReplaceInRange(), new function that replaces target strings in a range with another string




Added: _ExcelSheetActivate(), to replace _ExcelSheetNumActivate() and _ExcelSheetNameActivate(); added extensive error handling

Added: _ExcelSheetDelete(), to replace _ExcelSheetNumDelete() and _ExcelSheetNameDelete(); added extensive error handling

Added: _ExcelSheetUsedRangeGet(), for finding the used range of a sheet, contributed by DaLiMan, revised by SEO

Added: _ExcelCellFormat(), for formatting a range of cells with parameters like wrapped text, text orientation, text indentation, etc.

Added: _ExcelCellMerge(), for merging (or unmerging) together all cells of a given range

Added: _ExcelReadArray(), for reading a number of cells straight into an array

Added: _ExcelInsert(), insert the clipboard data at a specified cell, shifting the existing contents left or down

Added: _ExcelBookOpenTxt(), for opening and parsing a text file as a worksheet




Removed: The functions that were depricated as of last release - _ExcelWriteCellA1(), _ExcelWriteCellR1C1(), _ExcelWriteFormulaA1(), _ExcelWriteFormulaR1C1(), _ExcelReadCellA1(), _ExcelReadCellR1C1(), _ExcelCopyA1(), _ExcelCopyR1C1(), _ExcelPasteA1(), _ExcelPasteR1C1(), _ExcelNumberFormatA1(), _ExcelNumberFormatR1C1(), _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline()




Removed: Headers for _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete() and _ExcelSheetNameDelete(); these functions will be removed entirely from the next release




Removed: _ExcelSheetUsedRowGet() and _ExcelSheetUsedColGet(), replaced by _ExcelSheetUsedRangeGet()




Things to be incorporated into next release:




Printing & document setup

Workbook & worksheet password/protection/encryption

Bordering will return, with better options




---------10/24/06----------

Update, v1.2




MAJOR UPDATE! Will most likely break existing scripts. Please update scripts accordingly.




Changed: All functions that accepted $sRange will now accept either A1 or R1C1 format. This means that all of these functions will have different parameters, and in most cases the order of the parameters will be different as well. Here's a list of changed functions, with new syntax:




_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)

_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)

_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelPaste($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sRangeOrRow, $iColumn = 1)

_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)

_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)

_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)

_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)

_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")

_ExcelFontGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)

_ExcelFontGetSize($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")

_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")




Changed: Several headers that had typos or were otherwise incorrect.




Changed: _ExcelColWidthSet($oExcel, $vColumn, $vWidth), _ExcelColWidthGet($oExcel, $vColumn), _ExcelRowHeightSet($oExcel, $vRow, $vHeight), _ExcelRowHeightGet($oExcel, $vRow) all now accept numbers as well as letters for column or row (A1 and R1C1 format)




Removed: All headers from functions that specify A1 or R1C1 addressing in their function names. The associated functions remain, but will be removed at next release. Here's the list of functions to be removed:




_ExcelWriteCellA1()

_ExcelWriteCellR1C1()

_ExcelWriteFormulaA1()

_ExcelWriteFormulaR1C1()

_ExcelReadCellA1()

_ExcelReadCellR1C1()

_ExcelCopyA1()

_ExcelCopyR1C1()

_ExcelPasteA1()

_ExcelPasteR1C1()

_ExcelNumberFormatA1()

_ExcelNumberFormatR1C1()




Removed: Headers for _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline(). These functions will be removed entirely from the next release.




Moved: _ExcelSortExtended() to working functions list, with thanks to DaLiMan




_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)




Added: _ExcelSheetUsedRowGet(), contributed by Stanley Lim, minor changes by SEO




Added: _ExcelSheetUsedColGet(), contributed by DaLiMan, based on _ExcelSheetUsedRowGet() by Stanely Lim, minor changes by SEO




Added: _ExcelFontSetProperties(), which replaces _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline()







---------10/13/06----------

Update, v1.11

Changed: _ExcelBookNew() has been drastically changed - please read its header!

Changed: _ExcelBookSaveAs() has had some important notes added to its header

Changed: Updated many comment headers

Fixed: Typos and bugs in _ExcelReadCellR1C1()

Added: _ExcelHyperlinkInsert() to working functions list

Added: Some functions to be written, _ExcelBookOpenTxt(), _ExcelCellFormat(), _ExcelCellMerge(), _ExcelPrintSheet(), _ExcelPrintRange(), _ExcelPrintSetup()

Added: Simple example file, ExcelCOM_UDF_Example01.au3







---------10/11/06----------

Update, v1.1

Updates: _ExcelPictureAdd(), _ExcelPictureAdjust(), _ExcelPictureScale(), _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete(), _ExcelSheetNameDelete(),

_ExcelCellColorSet(), _ExcelFontColorGet() all updated, tested, and added to working functions list.




Comments (help) updated for most functions.




Changed names of the following functions to match big_daddy's suggestion: _ExcelColumnInsert(), _ExcelColumnDelete(), _ExcelRowInsert(), _ExcelRowDelete(), ExcelHyperlinkInsert(), _ExcelFontSet(), _ExcelFontGet(), _ExcelFontSetSize(), _ExcelFontGetSize(), _ExcelFontSetColor(), ExcelFontGetColor(), _ExcelCellColorSet(), _ExcelCellColorGet(), ExcelHorizontalAlignSet(), _ExcelVerticalAlignSet(), _ExcelColWidthSet(), _ExcelColWidthGet(), _ExcelRowHeightSet(), _ExcelRowHeightGet(), _ExcelSheetMove(), _ExcelSheetAddNew(), _ExcelSheetNameGet(), _ExcelSheetNameSet(), _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete(), _ExcelSheetNameDelete(), _ExcelSheetList().




---------10/11/06----------

Update, v1.01

Updates: Changed function names for _Excel document add/save/new functions, removed _ExcelInit(), gave _ExcelBookOpen() and _ExcelBookNew() the ability to return object handle.

Additions: None




---------10/10/06----------

Initial release, v1.0

Updates: None

Additions: None

 

 

LIST OF WORKING FUNCTIONS (count = 68)

expandcollapse popup

_ExcelBookNew($fVisible = 1)


_ExcelBookAttach($s_string, $s_mode = "FilePath")


_ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")

_ExcelBookOpenTxt($sFilePath, $sDelimiter = ",", $iStartRow = 1, $iDataType = 1, $iTextQualifier = 1, $fConsecDelim = False, $fVisible = 1)

_ExcelBookSave($oExcel, $fAlerts = 0)

_ExcelBookSaveAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, $iConflictResolution = 2)

_ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)

_ExcelShow($oExcel)

_ExcelHide($oExcel)

_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)

_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)

_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0)

_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)

_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelPaste($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelInsert($oExcel, $sRangeOrRow, $iColumn = 1, $iShiftDirection = -4121)

_ExcelRowDelete($oExcel, $iRow, $iNumRows = 1)

_ExcelColumnDelete($oExcel, $iColumn, $iNumCols = 1)

_ExcelRowInsert($oExcel, $iRow, $iNumRows = 1)

_ExcelColumnInsert($oExcel, $iColumn, $iNumCols = 1)

_ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)

_ExcelPictureAdjust($oPicture, $iHorizontal, $iVertical, $iRotation = 0)

_ExcelPictureScale($oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $fScaleOrigWidth = True, $fScaleOrigHeight = True, $iScaleFrom = 0)

_ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)

_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sScreenTip, $sRangeOrRow, $iColumn = 1)

_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)

_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

_ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "")

_ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "", $fReplaceFormat = False)

_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)

_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)

_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelSplitWindow($oExcel, $iSplitRow, $iSplitColumn)

_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)

_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")

_ExcelFontGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)

_ExcelFontGetSize($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")

_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")

_ExcelColWidthSet($oExcel, $vColumn, $vWidth)

_ExcelColWidthGet($oExcel, $vColumn)

_ExcelRowHeightSet($oExcel, $iRow, $vHeight)

_ExcelRowHeightGet($oExcel, $iRow)

_ExcelSheetMove($oExcel, $vMoveSheet, $vRelativeSheet = 1, $fBefore = True)

_ExcelSheetAddNew($oExcel, $sName = "")

_ExcelSheetNameGet($oExcel)

_ExcelSheetNameSet($oExcel, $sSheetName)

_ExcelSheetActivate($oExcel, $vSheet)

_ExcelSheetDelete($oExcel, $vSheet, $fAlerts = False)

_ExcelSheetList($oExcel)

_ExcelSheetUsedRangeGet($oExcel, $vSheet)

_ExcelCellFormat($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fWrapText = False, $iOrientation = 0, $fAddIndent = False, $iIndentLevel = 0, $fShrinkToFit = False)

_ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelPrintRange($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, $fCollate = False, $sPrToFileName = "")

_ExcelPrintSheet($oExcel, $vSheet, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, _

_ExcelBookPropertiesGet($oExcel)

_ExcelBookPropertiesSet($oExcel, $sAuthor = "", $sTitle = "", $sSubject = "")

_ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)

_ExcelWriteSheetFromArray($oExcel, ByRef $aArray, $iStartRow = 1, $iStartColumn = 1, $iRowBase = 1, $iColBase = 1)

LIST OF FUNCTIONS IN NEED OF TESTING AND/OR FURTHER DEVELOPMENT (count = 0)

None at present.

LIST OF FUNCTIONS TO BE WRITTEN (count = 0)

_ExcelPrintSetup() for more printing options


More options for shared workbooks

Bordering will be updated to be even more flexible
Latest Version - v1.5 (07/18/2008)

Excel.zip

Note: This thread is being maintained by litlmike while LocoDarwin is away

ExcelCOM_UDF.au3 (v1.4)

Attached examples:

ExcelCOM_UDF_Example01.au3 --- Basic usage (create book, populate cells, read cells, save, quit)

ExcelCOM_UDF_Example02.au3 --- Basic usage (create book, populate table, add formulas, save, quit)

Edited April 3, 2017 by Melba23
Reformatted due to forum upgrades

[big_daddy]

1. Instead of opening and closing the document for each call, my routines offer more flexibility.

That is what I disliked about randallc's library.

Looks like youre off to a great start, but I dislike your choice for naming functions.

Instead of:

_ExcelInit()
_ExcelShow($oExcel)
_ExcelHide($oExcel)oÝ÷ Ù*&zØb   b殶­seôW6VÄ7&VFRb33c·5ôfÆUFÒgV÷C²gV÷C²Âb33c¶e÷G'GF6ÒÂb33c¶e÷f6&ÆRÒÂb33c¶e÷F¶Tfö7W2Ò

Take a look at my Word.au3 and Dale's IE.au3 libraries. If you follow the standards used in those two libraries you will stand a better chance at getting it added into AutoIt's UDFs.

[Locodarwin]

That is what I disliked about randallc's library.

Looks like youre off to a great start, but I dislike your choice for naming functions.

Instead of:

_ExcelInit()
_ExcelShow($oExcel)
_ExcelHide($oExcel)oÝ÷ Ù*&zØb   b殶­seôW6VÄ7&VFRb33c·5ôfÆUFÒgV÷C²gV÷C²Âb33c¶e÷G'GF6ÒÂb33c¶e÷f6&ÆRÒÂb33c¶e÷F¶Tfö7W2ÒoÝ÷ ØêÚºÚ"µÍÌÍÛÑ^Ù[HÑ^Ù[[]

HÈÜX]HH^Ù[ÓÓHØXÝÑ^Ù[ÛÚÓÜ[  ÌÍÛÑ^Ù[    ][ÝÐÎÌLÙ^Ý[Ù[KÉ][ÝËJHÈÜ[^Ý[ÈÛÚÈ[ÓÓHØXÝXZÙH]ÚXB

I could combine open and new into an "_ExcelCreate()" function, but leaving them as two separate functions makes it easier to determine at a glance which operation is occuring, in my opinion.

I can't think of a situation where keeping the COM object creation separate from the opening/creation of a workbook would be absolutely necessary, so I think I'll probably combine functionality there and just have the two functions return the object identifier.

Are you referring to anything other than what you've stated? If so, let me know. I've been otherwise following the standards pretty closely, to the best of my knowledge.

Again, thanks for the function name tips. I'll update them shortly.

-S

Edited October 11, 2006 by Locodarwin

[Locodarwin]

Updated original post. v1.01 attached.

[Locodarwin]

Updated original post. v1.10 attached.

[DaleHohm]

Take a look at my Word.au3 and Dale's IE.au3 libraries. If you follow the standards used in those two libraries you will stand a better chance at getting it added into AutoIt's UDFs.

Just as I offered to big_daddy, please feel free to take/steal anything that you would like from IE.au3. I won't claim that it is perfect, but a lot of thought went into the architecture of it and if your structure ends up similar to IE.au3 and Word.au3 it will help with learning curves overall.

Dale

[randallc]

hi,

Looks great.

the things I would enquire about from my bitter experience!;

1. Can these udfs "suppress" the message about high security etc?

2. Will a result be sent still to a proxy sheet if "read only" sheet used for writing?

3. Are you able to put your udf in a zip with a heap of examples?

4. [EDIt] - I see your plans fort charts -thanks

Best, randall

Edited October 12, 2006 by randallc

[Locodarwin]

@Dale: Thanks for the offer - I'll see what I can translate over. There are some differences in my approach due to the differences inherent in Excel COM vs. IE COM, but nothing serious. I am particularly inspired by your heavy-duty error handling, so that will probably be the most important translation. Congratulations on getting your IE UDF into the bundle, by the way. I haven't had a reason to use it yet (though some wireless routers with no telnet interfaces here in the office do need some automation), but I have been following its development fairly closely.

@Randallc: I'll address each question.

1. I presume by high security, you mean macro security. That's a challenge that my code will only partially address at this time. I guess the quick answer for someone asking me how to supress macro security warnings is not to use macros at all anymore and to convert all macro/VBA code into ExcelCOM code. That bypasses the problem nicely. I realize that's not practical in all situations, so I'll look into other approaches. The very first approach I used was a separate little "waiter" program that sat in the background waiting for the macro security window to pop up so that it could "click" the appropriate button. Not only is that an ugly solution, it's highly insecure. Any ideas?

If you also meant document protection, there are a few different ways to approach the methodology, and I've been poking around with it somewhat. In the days ahead I'll be presenting my ideas, and I'll leave it up to the experts to yay/nay what they feel works and what they feel does not work.

2. I'm not sure how to handle this situation, or if I should bother with it. I guess the big question is, how much are we going to assume the user should know about the documents he is trying to edit? Should we consider such a case an error, and if not, how else can we handle it? Should we force the document into read/write mode automatically, or just deal with the ghost copy it makes by default?

3. Yes, as time goes on I'll be providing extensive examples, most likely in a zip file. I do plan to submit it as an "official" UDF, after all. It's a big job for one guy, as you well know. My UDF is already 70k+ and contains over 60 functions. 56 of those functions have been tested and have full comment headers. I would welcome help in any form, especially from folks as esteemed as yourself.

4. Charts. I love them. I have to have them. I have seen how you've approached them in your code, and the workarounds you've had to put in seem like they were made only after total COM trial and error exhaustion. I have a ton of VBA code I've been bashing my head against for some time now, trying to convert it into AutoIt COM code. Finally, after a few months of heartache, I've been successful. The whole key to programming charts with COM is to use the R1C1 address format. Try it on your own and you'll see what I mean. Things that don't work at all with A1 format suddenly work as expected when converted to R1C1. I'm not kidding - it's that simple.

So I'll be rolling out the Excel Chart COM stuff sometime soon. I've also made some headway with the Pivot Table stuff. I have a CPA buddy who is very interested in automating pivot table reports with AutoIt.

Thanks for your input, guys.

-S

[randallc]

1. I presume by high security, you mean macro security. Any ideas?

Hi,

I had a parameter "$i_suppress, which led to suppression of that reminder; it is in my code, ; i'd have to look to remember it"

[i think I created a new instance of Excel to run the displayalerts stuff, and closed it again after;

If Not IsObj ($oxl) and $i_Suppress Then    $oxl=ObjCreate("Excel.Application")
    if $i_Suppress then 
        $oxl.DisplayAlerts = 0
        $oxl.ScreenUpdating = 0
    EndIfoÝ÷ Ù«­¢+Ø%%9½Ð%Í=¨ ÀÌØí½á°¤¹ÀÌØí¥}MÕÁÁÉÍÌQ¡¸$ÀÌØí½á°õ=©
ÉÑ ÅÕ½Ðíá°¹ÁÁ±¥Ñ¥½¸ÅÕ½Ðì¤($$$%¥%Í=¨ ÀÌØí½á°¤Ñ¡¸($$$$$ÀÌØí½á°¹¥ÍÁ±å±ÉÑÌôÄ($$$$$ÀÌØí½á°¹MɹUÁÑ¥¹ôÄ($$$$$ÀÌØí½á°¹EÕ¥Ð($$$$$ÀÌØí½á°ôÅÕ½ÐìÅÕ½Ðì($$$%¹%

]

4. Charts. the workarounds

The workarounds I had date back to when AutoIt did NOT allow more than 1 parameter in a COM command![same applied to extended sort]; it was fixed in beta about 3.1.1.70 or 80, i think!; just so you understand why I resorted to vbs. just never got around to converting it.....

Best, Randall

[Locodarwin]

Hi,

I had a parameter "$i_suppress, which led to suppression of that reminder; it is in my code, ; i'd have to look to remember it"

[i think I created a new instance of Excel to run the displayalerts stuff, and closed it again after;

If Not IsObj ($oxl) and $i_Suppress Then    $oxl=ObjCreate("Excel.Application")
    if $i_Suppress then 
        $oxl.DisplayAlerts = 0
        $oxl.ScreenUpdating = 0
    EndIfoÝ÷ Ù«­¢+Ø%%9½Ð%Í=¨ ÀÌØí½á°¤¹ÀÌØí¥}MÕÁÁÉÍÌQ¡¸$ÀÌØí½á°õ=©
ÉÑ ÅÕ½Ðíá°¹ÁÁ±¥Ñ¥½¸ÅÕ½Ðì¤($$$%¥%Í=¨ ÀÌØí½á°¤Ñ¡¸($$$$$ÀÌØí½á°¹¥ÍÁ±å±ÉÑÌôÄ($$$$$ÀÌØí½á°¹MɹUÁÑ¥¹ôÄ($$$$$ÀÌØí½á°¹EÕ¥Ð($$$$$ÀÌØí½á°ôÅÕ½ÐìÅÕ½Ðì($$$%¹%oÝ÷ Ù8^Âäjº.Û§]j×iÉ-£^­ ]ÓNM©e£  ¨­ëaj}ij¶¦z׫v8Ç(f§w}ìjg¦byÛh{^ײíܱø±yاmëZiº.·}uïJ+óH­)äßxî²Û(ʮ׫²Ö§wr"·¬¢»^vÚ/nÈî²ÙÞ½êà¢Ö«¢éݶ(÷«¶)àÐ^²ÔZÖ¥ú®¢×jwZTájÝý²Ø^ëa¡Ò.±éèÂ)Ú}ì(~Ø^iº/yû§rبζ²êkzËÊek %z»ljwRr·J]jا*2¢íý­ëÊ)ඬjÊÆò¥«,zËazf®rêâ·'bjZ ¹éè«mz¼!jÛaxjèºh'2Â!j÷§ßÛmzË^vØb±»­#oy·Ú%v+]¡ë'ßÛp¢¹"ëÞ¯+"¶æ­ÈßÙemèZ¦¢~+]¡ëÚÞªÝxªí°'£!jܨºg¦èºÐ2«jg­z»Ûaz«¨´7jémªê-¥ªÚë^®ËbWhzÉ÷öÜ(®JâbëÞ¯'±ãj|!W­êÞj·¬¶)e¶§Ëzh§¢,²&åzÚ¢)ÀÕú+«B¢{"uêíçèZ0x0¢¹"'^µ«­¢+Ø(ÀÌØí
¡ÉÑ=©Ð¹MÉ¥Í
½±±Ñ¥½¸ Ĥ¹aY±ÕÌôÅÕ½ÐìôÌäí
¡ÉÐÑÌäìÌÌíHÉÈéHÉÄàÄÅÕ½Ðì(ÀÌØí
¡ÉÑ=©Ð¹MÉ¥Í
½±±Ñ¥½¸ Ĥ¹Y±ÕÌôÅÕ½ÐìôÌäí
¡ÉÐÑÌäìÌÌíHÍÈéHÍÄàÄÅÕ½Ðì(

The same code, in the same context, but with A1 addressing, fails with a COM error regarding an invalid range. Yet, if you convert the code to VBA (which is what this snippet was originally converted from), no COM error is produced using either A1 or R1C1.

This is specific to a number of Chart properties & methods, particularly SeriesCollections and Axis naming.

Ah well, it's neither here nor there. At this point it's just an anecdote.

-S

[randallc]

no matter what the Group policy is? I haven't tested this, but I've been told it doesn't work in every situation.

not sure about that....

$ChartObject.SeriesCollection(1).XValues = "='Chart Data'!R2C2:R2C181"
$ChartObject.SeriesCollection(1).Values = "='Chart Data'!R3C2:R3C181"

The same code, in the same context, but with A1 addressing, fails with a COM error regarding an invalid range. Yet, if you convert the code to VBA (which is what this snippet was originally converted from), no COM error is produced using either A1 or R1C1.

Might be worth a PM to sVenP; I think he runs the COM stuff and fixed the parameters problem previously?

Best, Randall

[MHz]

...

Meanwhile, there are still things seemingly impossible to do in A1 format. Consider the following working snippet:

$ChartObject.SeriesCollection(1).XValues = "='Chart Data'!R2C2:R2C181"
$ChartObject.SeriesCollection(1).Values = "='Chart Data'!R3C2:R3C181"

The same code, in the same context, but with A1 addressing, fails with a COM error regarding an invalid range. Yet, if you convert the code to VBA (which is what this snippet was originally converted from), no COM error is produced using either A1 or R1C1.

...

Hi Locodarwin,

Looks like you are having problems with the write to object concept with AutoIt. I have lodged a report on this and a soon to release AutoIt beta may have the fix by SvenP. Others have raised issues like yours to attention of SvenP. My issue was with the dictionary object but does involve perhaps a common issue with writing to an object as yours. So, I thought I should warn you of this instead of you doing some radical workaround for the issue.

I reported here...and hopefully issues will be solved.

http://www.autoitscript.com/forum/index.ph...st&p=249719

Try the future beta's and would suggest reporting it if the write issue continues so SvenP can fix it.

[Locodarwin]

MHz: Good call - that could very well be the issue. I'll check into it once the next beta releases.

-S

[Locodarwin]

Updated original post. v1.11 attached.

Important! _ExcelBookNew() drastically altered. It no longer takes the filepath or overwrite parameters. Instead, it creates but does not then quickly save a workbook. Use the _ExcelBookSave() or _ExcelBookSaveAs() functions to save the workbook after creating.

;===============================================================================
;
; Description:      Creates new workbook in previously created Excel object.
; Syntax:           $oExcel = _ExcelBookNew($fVisible = 1)
; Parameter(s):     $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible)
; Requirement(s):   None
; Return Value(s):  On Success - Returns new object identifier
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Unable to create the Excel COM object
;                       @error=2 - $fVisible parameter is not a number
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================

Several other minor changes - please see changelog in first post.

-S

Edited October 13, 2006 by Locodarwin

[Locodarwin]

A simple "example use" script has been attached to the first post. More examples to follow.

-S

[randallc]

Hi,

I like the first example...

The duplication seems a lot in the UDF..?

What about all those commands with alternatives be changed (and remove the actual current functions; replace with code?) [concept only..]

Func _ExcelWriteFormula($oExcel, $sFormula, $iRangeOrColumn, $iRow=1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    if StringInStr($iRangeOrColumn,":") or StringRegExp($iRangeOrColumn, "[A-Z,a-z]", 0) then 
        Return _ExcelWriteFormulaA1($oExcel, $iRangeOrColumn, $sFormula)
    Else
        Return _ExcelWriteFormulaR1C1($oExcel, $iRow, $iRangeOrColumn, $sFormula)
    EndIf
;~  Return 1
EndFunc ;==>_ExcelWriteFormula
oÝ÷ Úð¢é]Éî
+«­¢+ٽȹ½Ð%Í9ÕµÈ ÀÌØí¥I¹=É
½±Õµ¸¤

?what do you think?

Randall

Edited October 14, 2006 by randallc

[randallc]

or..

Func _ExcelWriteFormula($oExcel, $sFormula, $iRangeOrRow, $iColumn=1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    if StringInStr($iRangeOrRow,":") or not IsNumber($iRangeOrRow) then 
;~  if StringInStr($iRangeOrColumn,":") or StringRegExp($iRangeOrColumn, "[A-Z,a-z]", 0) then 
        Return _ExcelWriteFormulaA1($oExcel, $iRangeOrRow, $sFormula)
    Else
        Return _ExcelWriteFormulaR1C1($oExcel, $iRangeOrRow, $iColumn, $sFormula)
    EndIf
EndFunc ;==>_ExcelWriteFormula

? Edited October 14, 2006 by randallc

[Locodarwin]

I like your StringRegExp() solution. Here's the all-code version:

Func _ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula
        Return 1
    Else
        $oExcel.Activesheet.Range($sRangeOrRow).Formula = $sFormula
        Return 1
    EndIf
EndFunc ;==>_ExcelWriteFormula

If others agree that this is a suitable final solution, I'll make it so.

That oughta be fun.

-S

[Stanley Lim]

Hi guys,

This is my small humble contribution to the world... :"> I have written 2 additional functions that will return the number of rows used in a given Excel Worksheet. Please feel free to add it into the ExcelCOM UDF library!

expandcollapse popup

;===============================================================================
;
; Description:    Return the number of rows ibeing used in the specified worksheet.
; Syntax:          _ExcelSheetNumUsedRowGet($oExcel, $sSheetName)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sSheetName - The sheet name to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns the number of rows being used in the specified worksheet.
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        Stanley Lim <voided_lim@yahoo.com.sg>
; Note(s):        None
;
;===============================================================================
Func _ExcelSheetNumUsedRowGet($oExcel, $iSheetNum)
    Dim $iRowCount
    
    If NOT IsObj($oExcel) Then
        $iRowCount = SetError(1, 0, 0)
    Else 
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.ScreenUpdating = 0
        $iRowCount = $oExcel.ActiveWorkbook.Sheets($iSheetNum).UsedRange.Rows.Count
        $oExcel.Application.DisplayAlerts = 1
        $oExcel.Application.ScreenUpdating = 1
    EndIf
    
    Return $iRowCount
    
EndFunc;==>_ExcelSheetNumUsedRowGet

;===============================================================================
;
; Description:    Return the number of rows ibeing used in the specified worksheet.
; Syntax:          _ExcelSheetNameUsedRowGet($oExcel, $sSheetName)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sSheetName - The sheet name to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns the number of rows being used in the specified worksheet.
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        Stanley Lim <voided_lim@yahoo.com.sg>
; Note(s):        None
;
;===============================================================================
Func _ExcelSheetNameUsedRowGet($oExcel, $sSheetName)
    Dim $iRowCount
    
    If NOT IsObj($oExcel) Then
        $iRowCount = SetError(1, 0, 0)
    Else 
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.ScreenUpdating = 0
        $iRowCount = $oExcel.ActiveWorkbook.Sheets($sSheetName).UsedRange.Rows.Count
        $oExcel.Application.DisplayAlerts = 1
        $oExcel.Application.ScreenUpdating = 1
    EndIf
    
    Return $iRowCount
    
EndFunc;==>_ExcelSheetNameUsedRowGet

[Locodarwin]

@Stanley: Excellent contributions, thank you. Those routines will be added to the UDF.

@Everyone: Per Randallc's suggestions and my speed-of-execution tests, I've decided to change the routines that require ranges so that each one will accept A1 and R1C1 addressing.

The end result is that routines like _ExcelWriteCellA1() and _ExcelWriteCellR1C1() will be combined into one routine, in this case _ExcelWriteCell(). Too, the parameters of many of these functions will change slightly - mostly in how they are ordered.

Why is this being done?

1. Like I stated in the first post, I have been trying to figure out the best way of dealing with the two addressing schemes. Since the A1 and R1C1 schemes have strengths and weaknesses on their own, I really wanted to keep them both. At the same time, I didn't want the number of functions (and the size of the UDF) to grow unmanageable. This solves that problem.

2. Using StringRegExp(), it's a relatively simple process to alter said functions in said manner. I performed a series of speed tests on StringRegExp(), using TimerDiff(), and found a relatively insignificant hit to script performance. However, the development of StringRegExp() appears to be in a sort of questionable state, so if for some reason it turns out not to be ideal, it'll only take a couple of simple search & replace commands to implement IsNumber() instead.

3. We end up with not only fewer functions, but simpler function names as well.

There is one down side. I will not be keeping the depricated functions in the code. I'll keep them in for one more release, but after that they'll be pulled. I apologize for the inconvenience this will cause.

The next release, with the indicated changes, will follow within a couple days.

-S