Sign in to follow this  
Followers 0

Yet Another -- ExcelCOM UDF

380 posts in this topic

Posted (edited)

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

CODE

---------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)

_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)

ExcelCOM_UDF.au3

ExcelCOM_UDF_Example01.au3

ExcelCOM_UDF_Example02.au3

Edited by big_daddy

Share this post


Link to post
Share on other sites



Posted

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.

Share this post


Link to post
Share on other sites

Posted (edited)

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 by Locodarwin

Share this post


Link to post
Share on other sites

Posted

Updated original post. v1.01 attached.

Share this post


Link to post
Share on other sites

Posted

Updated original post. v1.10 attached.

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

Posted

@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. :lmao: 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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

...

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.

:lmao:

Share this post


Link to post
Share on other sites

Posted

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

-S

Share this post


Link to post
Share on other sites

Posted (edited)

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 by Locodarwin

Share this post


Link to post
Share on other sites

Posted

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

-S

Share this post


Link to post
Share on other sites

Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

Posted

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. :lmao:

-S

Share this post


Link to post
Share on other sites

Posted

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! :lmao:

;===============================================================================
;
; 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

Share this post


Link to post
Share on other sites

Posted

@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

Share this post


Link to post
Share on other sites

Posted

A suggestion on function naming... think ahead to the helpfile and the fact that the fuctions sort in alpha order. Therefore you might want _ExcelCellWrite and _ExcelCellRead instead of _ExcelWriteCell etc...

Dale

Share this post


Link to post
Share on other sites

Posted

A suggestion on function naming... think ahead to the helpfile and the fact that the fuctions sort in alpha order. Therefore you might want _ExcelCellWrite and _ExcelCellRead instead of _ExcelWriteCell etc...

Dale

Actually, after big_daddy's suggestions, I spent a great deal of time looking at and thinking about the naming and order of the functions, both from a logical standpoint and an alphabetical standpoint.

Using your examples, _ExcelWrite**** and _ExcelRead**** are better as they are because when you group them up logically (and, coincidentally, alphabetically) they make more sense - at least to me.

Consider:

_ExcelWriteArray()

_ExcelWriteCell()

_ExcelWriteFormula()

vs.

_ExcelArrayWrite()

_ExcelCellWrite()

_ExcelFormulaWrite()

On write and read functions, the emphasis is placed on the action. The same is true for many of the internal functions (and most UDFs) as well. They group the application first, then the action, then the specific noun to perform the action upon:

DriveGetDrive()

DriveGetFilesystem()

DriveGetLabel()

DriveGetSerial()

DriveGetType()

This jives with _ExcelWriteCell().

Most of the rest of my functions are named according to your specification, which is _ApplicationNounVerbSpecificnoun(). However, even your functions do not follow that formula in every case. So, like you, I don't feel that formula is the best naming scheme in every instance.

-S

Share this post


Link to post
Share on other sites

Posted

Hi Locodarwin,

This looks very promissing....I hope in time it will make it into the UDF library!!! :lmao:

I'm now using Randallc's ExcelCom but he gave me the link to your's.

I was trying to sort my sheet, but I didnt want my header to be sorted as well.

Now in your UDF I saw the func _ExcelSortExtended() but I cannot get it to work.

Can you help me, I tried several things but this was my best shot....

#include"P:\Downloads\_AutoIt\Excel\ExcelDarwin\ExcelCOM_UDF.au3"
;****** Create Blank Files, then add some data to XL file as example in column "Z"  on
;~ $XLInsert = FileOpenDialog("Selecteer OTIS prijslijst","P:\Test\Excel\","Excel(.xls)",-1,"OTIS_2*")

$ReadXLPath="P:\Test\Excel\Otis_TradeHistory.xls"

$oExcel = _ExcelBookOpen($ReadXLPath, 1)
;~ _ExcelSort($oExcel, $sRange, $sKey, $iDirection, $iHeader, $fMatchCase, $iOrientation, $iDataOption)
_ExcelSortExtended($oExcel, "A:Z", "A", 1, $xlYes, False, "xlSortColumns", "xlSortNormal")oÝ÷ Ø	l ²Úòx-¡Ê)É©Ý¥«-yúèWâëhjz-êåG­+¢é]¶¬mêh²È觶ºÛ«y«­¢+Ø(¥¹±ÕÅÕ½Ðí@èÀäÈí½Ý¹±½ÌÀäÈí}Õѽ%ÐÀäÈíá°ÀäÈíá±ÉÝ¥¸ÀäÈíá±
=5}U¹ÔÌÅÕ½Ðì((ì
ÉѹÜݽɭ½½¬°Ù¥Í¥±°¹ÉÑÕɸ¸½©Ð¡¹±Ñ¼¥Ð(ÀÌØí½á°ô}á±	½½­9Ü ¤(ÀÌØí½á°Èô}á±	½½­9Ü ¤()}á±]É¥Ñ
±±Ä ÀÌØí½á°°ÅÕ½ÐíÈÌÅÕ½Ðì°ÅÕ½Ðí¹¥°ÅÕ½Ðì¤()}á±
½ÁåÄ ÀÌØí½á°°ÅÕ½ÐíÈÌÅÕ½Ðì¤)}á±AÍÑÄ ÀÌØí½á°È°ÅÕ½ÐíÄÅÕ½Ðì¤(oÝ÷ ØôÅÇ¥Jí^×É¢²Ê^W{az̧µ¬^ÆZ«b¢tyÊ)É·¥£ë¢l¨ºµb²Ù^jëh×6
;===============================================================================
;
; Description:      Performs a more advanced sort on a range.
; Syntax:    ===>       [b]_ExcelSort[/b]($oExcel, $sRange, $sKey, $iDirection, $iHeader, $fMatchCase, $iOrientation, $iDataOption)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;					$sRange - The sort range in A1 format
;					$sKey - The key column or row to sort by (in A1 format)
;					$iDirection - Sort direction (1=Ascending, 2=Descending)
;					$iHeader - Assume sort data has a header?  ($xlYes|$xlNo|$xlGuess)
;					$fMatchCase - Match case when performing sort (True|False)
;					$iOrientation - Specify how sort data is arranged (xlSortRows|xlSortColumns)
;					$iDataOption - Specify how sort will treat data (xlSortTextAsNumbers|xlSortNormal)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;						@error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          This sort routine will not function properly with pivot tables.  Please
;					use the pivot table soring functions instead.
;
;===============================================================================
Func _ExcelSortExtended($oExcel, $sRange, $sKey, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 1, $iDataOption = 0)
	If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
	; A bunch of variable checks	
	$oExcel.Range($sRange).Sort ($oExcel.Range($sKey), $iDirection, $oExcel.Range($sKey), Default, $iDirection, _ 
	$oExcel.Range($sKey), $iDirection, $iHeader, True, $fMatchCase, $iOrientation, Default, $iDataOption)
	;$oExcel.Range($sRange).Sort ($oExcel.Range($sKey), $iDirection, Default, Default, Default, Default, Default, _
	;$iHeader, Default, $fMatchCase, $iOrientation, Default, $iDataOption)

; Sort(rangeobject, xlAscending|Descending, Default, Default, Default, Default, Default, header(xlYes-xlNo-xlGuess), Default, _
; 	Matchcase(True-False), Orientation(xlSortRows|xlSortColumns), Default, DataOption(xlSortTextAsNumbers|xlSortNormal))
EndFunc	;==>

Share this post


Link to post
Share on other sites

Posted

Hello, DaLiMan,

_ExcelSortExtended() is still in the development stage - so don't take anything about it as gospel, especially any "mizpellinz" and other bugs you find. I'm aware that it is only partially functional at this time.

When specifying a $sKey and $sRange, use the full A1 representation of the key cell and the range. For your key cell, use something like "A1" or "A2" instead of "A." Which row you specify isn't important. For your range, make sure you specify one that includes columns and rows (i.e. "A1:Z999") instead of merely columns. If you don't know at runtime where your sort row will end....well, do your best to estimate or track it somehow. I'm pretty sure column-only ranges are supposed to work as you've specified them (they do in VBA) but for some reason they don't always work when using AutoIt COM. I don't think AutoIt is to blame - I have seen enough evidence to believe there are problems with Excel's COM dispatch in general. It's buggy, I'm told.

I have a sneaking suspicion that when I add R1C1 addressing to _ExcelSortExtended() it will start working correctly, as if by magic.

Also, in your function call you pass strings for the $iOrientation and $iDataOption fields. Those parameters should be integer constants. The Excel constants you want to use (xlSortColumns and xlSortNormal) are defined in the UDF already - specify them the AutoIt way by placing the dollar sign in front of them.

So here's the final product for your function call:

_ExcelSortExtended($oExcel, "A1:Z999", "A1", 1, $xlYes, False, $xlSortColumns, $xlSortNormal)

Let me know if that works. If not, try removing some of the optional parameters, starting from the last parameter and working your way toward the first.

If it still doesn't work the way you need it to, I'll raise its priority.

-S

P.S. As for your second code example, copying and pasting values between two open workbook objects, I'm unsure why it doesn't already work. I don't have that problem when copying and pasting between Word and Excel (I also have a Word UDF in the works), but I did verify that your code fails to work as expected, so I'll have a look at it. Stay tuned.

Share this post


Link to post
Share on other sites

Posted

I'd love to see this get into the standard release... I'm just wondering though, will you and and Randallc "put your heads together"? I'd hate to see you two competing to be included in the stable release, plus it's confusing for us folks looking for an excel UDF... which one do I use??

Anyway, just a thought, continue good sir!

Share this post


Link to post
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
Sign in to follow this  
Followers 0




  • Recently Browsing   0 members

    No registered users viewing this page.