DaleHohm Posted October 16, 2006 Posted October 16, 2006 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 Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model Automate input type=file (Related) Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded Better Better? IE.au3 issues with Vista - Workarounds SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead? Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble
Locodarwin Posted October 16, 2006 Author Posted October 16, 2006 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...DaleActually, 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 (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
DaLiMan Posted October 20, 2006 Posted October 20, 2006 Hi Locodarwin, This looks very promissing....I hope in time it will make it into the UDF library!!! 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.... expandcollapse popup#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 ;==>
Locodarwin Posted October 20, 2006 Author Posted October 20, 2006 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. (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
fisofo Posted October 20, 2006 Posted October 20, 2006 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!
Locodarwin Posted October 21, 2006 Author Posted October 21, 2006 Fisofo, I can't speak for Randallc, but my impression is that he did not originally intend for his ExcelCOM work to be included with the AutoIt UDFs. It would have to be substantially rewritten to meet the UDF requirements. My UDF attempt is not meant to compete with any other existing project. I would contribute to any project that aims to add ExcelCOM wrappers to the user-contributed UDFs - especially sooner than later. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
fisofo Posted October 21, 2006 Posted October 21, 2006 Fisofo,I can't speak for Randallc, but my impression is that he did not originally intend for his ExcelCOM work to be included with the AutoIt UDFs. It would have to be substantially rewritten to meet the UDF requirements.My UDF attempt is not meant to compete with any other existing project.I would contribute to any project that aims to add ExcelCOM wrappers to the user-contributed UDFs - especially sooner than later.-SGotcha, that's good enough for me! And I must say I've started using your functions and they're working great, good job! I'll let you know if I run into any bugs or get any "features" ideas.
fisofo Posted October 21, 2006 Posted October 21, 2006 (edited) I have a sneaking suspicion that when I add R1C1 addressing to _ExcelSortExtended() it will start working correctly, as if by magic. R1C1 addressing will be great for the simplified sort as well, would it be like this? I suppose if you're consolidating A1 as well though, this would need to be combined with the current one. Func _ExcelSortR1C1($oExcel, $iRowStart, $iColStart, $iRowEnd, $iColEnd, $iColKey, $iDirection) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $iRowStart < 1 Then Return SetError(2, 0, 0) If $iColStart < 1 Then Return SetError(2, 0, 0) If $iRowEnd < 1 Then Return SetError(2, 0, 0) If $iColEnd < 1 Then Return SetError(2, 0, 0) With $oExcel.ActiveSheet .Range(.Cells($iRowStart, $iColStart), .Cells($iRowEnd, $iColEnd)).Sort (.Cells(1, $iColKey), $iDirection) EndWith EndFunc ;==>_ExcelSortR1C1 Edit 1:Oops, that $sKey was still in A1. Not sure if i've got this format right... i'll test it out. Edit 2:Okay, I think I got it. I realized that $iRowKey should really be unnecessary, just replace it with a "1" in the function. I also had a .cell in a .range to define a cell Heh, I'm still a noob at AutoIt, just trying to be helpful Edited October 21, 2006 by fisofo
fisofo Posted October 21, 2006 Posted October 21, 2006 documentation correction: ExcelFontColorSet in first post vs ExcelFontSetColor as actual function question: How about the ability to grab data from an excel sheet and copy it (including formatting) to be pasted into something else? I'm thinking maybe a "Range.Select" similar in vb code and then a selection.copy?
Locodarwin Posted October 21, 2006 Author Posted October 21, 2006 Hello, Fisofo,Thanks for the corrections. Any help is appreciated. The header comments in particular are most keen for some proofreading, as I hurried through them somewhat.R1C1 addressing will be great for the simplified sort as well, would it be like this? I suppose if you're consolidating A1 as well though, this would need to be combined with the current one.Actually, as I stated in a previous post, I'm revamping all of the functions that accept ranges so that they'll accept both R1C1 and A1 addressing - so yes, what you've written (which is quite good) would need to be consolidated with the A1 code. I've been pretty busy lately, so it's not happening as fast as I had hoped, but I'm getting there. Expect all said functions to work a little differently.How about the ability to grab data from an excel sheet and copy it (including formatting) to be pasted into something else? I'm thinking maybe a "Range.Select" similar in vb code and then a selection.copy?The _ExcelCopy() function will put a range into the clipboard exactly the way you suggested. The script writer can then use other native AutoIt commands like ClipGet() to put it into other applications, or _ExcelPaste() to put it into another workbook/worksheet. Note that copying between workbooks seems to be broken at the moment.I'm not sure if the _ExcelCopy() and _ExcelPaste() functions will retain formatting as written; if not, it's a simple matter to add a parameter that will bring the formatting over as well. If you could test it out, I'd be indebted.-S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
fisofo Posted October 21, 2006 Posted October 21, 2006 (edited) I've been pretty busy lately, so it's not happening as fast as I had hoped, but I'm getting there. Expect all said functions to work a little differently.Sounds good, I definitely can understand that... gotta pay the bills I'm not sure if the _ExcelCopy() and _ExcelPaste() functions will retain formatting as written; if not, it's a simple matter to add a parameter that will bring the formatting over as well. If you could test it out, I'd be indebted.No problem! I just used both of them and they work quite well. Formatting comes along very nicely... can't believe I didn't notice the function sitting there earlier Edited October 21, 2006 by fisofo
randallc Posted October 22, 2006 Posted October 22, 2006 (edited) Hi,Locardwin is correct; I had just wanted to demonstrate the feasability to encourage someone to do it well.I can't speak for Randallc, but my impression is that he did not originally intend for his ExcelCOM work to be includedIn my excel com there is a notePlease note; this is provided "as is" and was only intended as a "stop-gap"My idea was to try to learn COM, had no idea about UDFs originally, and people kept wanting additions! I have now added a link to this "another" UDF from my base post, recommending it for changing over for the future (and now if the function needed is there; charts etc still to come.. from Locardwin)Best, randall Edited October 22, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
DaLiMan Posted October 23, 2006 Posted October 23, 2006 _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.Hi Locodarwin,I noticed the 'development' - status for this function, but I really needed it so I just had to trie ...About the mizpellinz, just thought I'd let you know. It would be a shame for al this hard work if it was placed in the standard UDF and had these misspellings.... So here's the final product for your function call:_ExcelSortExtended($oExcel, "A1:Z999", "A1", 1, $xlYes, False, $xlSortColumns, $xlSortNormal)Thanx Locodarwin, this works great.I also tried something like "A1:Z999" but never noticed the $xlSortColumns......so I never would have figure it out. :"> For nowing the rows to sort at runtime it will be nice to have a function that will look for the last column and last row....(please don't flame me if it already exists, I haven't looked that hard yet.) But if anybody can set me on it's trail...... 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.Bummer....Just what I need most. Does this also include searching in 2 different files? (I mean, does this have the same problems?)I need to check data in 2 files and then add the data missing from 1 to the other....I'll stay tuned..... Thanx for all your hard work in this great UDF.
DaLiMan Posted October 23, 2006 Posted October 23, 2006 (edited) 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 Just found Stanley Lim's entry and found it very usefull. So I changed Row into Column and had exactly what I was looking for. maybe also a nice addition to the next release?? expandcollapse popup;=============================================================================== ; ; Description: Return the number of rows ibeing used in the specified worksheet. ; Syntax: _ExcelSheetNumUsedColGet($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): DaLiMan ; Note(s): Based on Stanley Lim's _ExcelSheetNumUsedRowGet() ; ;=============================================================================== Func _ExcelSheetNumUsedColGet($oExcel, $iSheetNum) Dim $iColCount If NOT IsObj($oExcel) Then $iColCount = SetError(1, 0, 0) Else $oExcel.Application.DisplayAlerts = 0 $oExcel.Application.ScreenUpdating = 0 $iColCount = $oExcel.ActiveWorkbook.Sheets($iSheetNum).UsedRange.Columns.Count $oExcel.Application.DisplayAlerts = 1 $oExcel.Application.ScreenUpdating = 1 EndIf Return $iColCount EndFunc;==>_ExcelSheetNumUsedColGet ;=============================================================================== ; ; Description: Return the number of rows ibeing used in the specified worksheet. ; Syntax: _ExcelSheetNameUsedColGet($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): DaLiMan ; Note(s): Based on Stanley Lim's _ExcelSheetNameUsedRowGet() ; ;=============================================================================== Func _ExcelSheetNameUsedColGet($oExcel, $sSheetName) Dim $iColCount If NOT IsObj($oExcel) Then $iColCount = SetError(1, 0, 0) Else $oExcel.Application.DisplayAlerts = 0 $oExcel.Application.ScreenUpdating = 0 $iColCount = $oExcel.ActiveWorkbook.Sheets($sSheetName).UsedRange.Columns.Count $oExcel.Application.DisplayAlerts = 1 $oExcel.Application.ScreenUpdating = 1 EndIf Return $iColCount EndFunc;==>_ExcelSheetNameUsedColGet Edit: Little typo... Edited October 23, 2006 by DaLiMan
DaLiMan Posted October 23, 2006 Posted October 23, 2006 @Locodarwin. I tested _ExcelSheetNameUsedColGet() and _ExcelSheetNumUsedColGet() further and found that they do the same. So maybe it should be called _ExcelSheetUsedColGet() instead, but I'll just leave that up to you. Also, when a sheet is empty it returns 1 ?!?! Maybe you could improve on that also.....
ptrex Posted October 23, 2006 Posted October 23, 2006 My little contribution to the Excel UDF This are all the constant needed to do Excel COM programming Excel_COM_constants.au3 Enjoy !! ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New
DaLiMan Posted October 23, 2006 Posted October 23, 2006 My little contribution to the Excel UDF This are all the constant needed to do Excel COM programming Excel_COM_constants.au3 Enjoy !! ptrexThis will save all of us a lot of searching!! thanx for sharing.
ptrex Posted October 23, 2006 Posted October 23, 2006 @DaLiManYou are welcome.I have 1 problem for you guys.When running the Excel UDF and the Word UDF in 1 script is generates errors when both have a COM error handler insde.Try to run this example :Excel + Word = Outlook ExampleI will post as well this message into the Word thread of BigDaddy, so he can check on his side as well.regardsptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New
Locodarwin Posted October 23, 2006 Author Posted October 23, 2006 Howdy, folks!@DaLiMan: Great - I'm glad _ExcelSortExtended() works. I guess I'll clean it up and add it to my "functions working" list. Also, thanks for the conversion of Stanley's contribution. All 4 of the functions will be added to the UDF, albeit in different form, which brings me to my next point...Considering the similarities between "Name" and "Num" in the functions that use those concepts to describe worksheets, and how easy it would be to combine Name and Num into the same function, I've decided to go ahead and consolidate them.So, for example, the following two functions:_ExcelSheetNumActivate($oExcel, $iSheetNum)_ExcelSheetNameActivate($oExcel, $sSheetName)...would be combined to create:_ExcelSheetActivate($oExcel, $vSheetNameOrNum)...thereby reducing the total number of functions & redundant code in the UDF.I seem to remember at one time having a good reason for keeping Name and Num in different routines; since I cannot for the life of me remember what that was, and since I can't think of a good reason at this very moment, either...I'll change them accordingly.Regarding _ExcelSheetNameUsedColGet() returning a 1 on a blank sheet: I don't really see a practical code fix or workaround for this issue, since Excel is deciding what that value is. I suggest the script writer find a way to determine if a sheet is empty through some other means.For example, if _ExcelSheetNameUsedColGet() returns 1 on your worksheet, check a few of the cells in column A or 1 that would normally be populated with data and see if they in fact are.@ptrex: Thanks for the Excel constants file. Actually, I have created constants files exactly like yours for Excel and Word. These can be found in the API Constants scripts & scraps thread located here:Gary Frost's API ConstantsRight around page 2, I think.Regarding COM error handling - I haven't included COM error handling in this UDF specifically to avoid conflicts like that, since only one such routine can be "installed" during run time. I've left it out for now. If I include it, it will be in the format Dale uses for his IE UDF - meaning, it'll be something you register manually only if you want it.-S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
ptrex Posted October 24, 2006 Posted October 24, 2006 (edited) @LocodarwinThanks for the feedback.I must have mist the API constants from Gary.I have tested the solutoin provided by Big_Daddy regarding the Error handling.See his WORD UDF.He suggests to put his Error handle on top of the main script. Everything after that will be caught by the Error handler and there will be no conflicts anymore.http://www.autoitscript.com/forum/index.ph...st&p=256264I tested this and it works fine.regards,ptrex Edited October 24, 2006 by ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now