Flandry Posted February 27, 2007 Posted February 27, 2007 This time I have read the syntax-description very well! ;-)I was not able to use the sort functions. I got everytime errors. An other sort command published by Locodarwin in this forum (without using the udf) has worked for me. http://www.autoitscript.com/forum/index.php?showtopic=35679Maybe the problem is caused by the different languages too as described by MrDev.Greetings
litlmike Posted February 27, 2007 Posted February 27, 2007 It was just missing from the Syntax comment line. Thanks for bringing it to my attention.The $sScreenTip parameter displays a text tip when the user hovers the mouse over the hyperlink. It can be any reasonably-sized string.-SOh ok. Thanks for the info. _ArrayPermute()_ArrayUnique()Excel.au3 UDF
MRDev Posted February 27, 2007 Posted February 27, 2007 @Flandry For $sKey you have to use a range string ("B1" or "A3" ...). With a single char ("B") the script will end with an error. I tested it with Office 2003 for both sort functions. It has nothing to do with the language. @Locodarwin Could this be because of different Office versions?
Locodarwin Posted February 28, 2007 Author Posted February 28, 2007 This behavior is as designed. My comment header contains an error in syntax. You have to use a single cell range for the key - a column or row designation is not enough. This is true no matter what version of Excel you're working with. I think it has to do with the algorithm Excel uses to decide which cell contains the header, and how many compound parts of the range are to be sorted. If a header is found, and you've requested it be used, Excel has to know which particular cell then begins/becomes the key for the full range. Just a guess. -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]
JohnBailey Posted March 21, 2007 Posted March 21, 2007 (edited) LocoDarwin, thanks for the great UDF!! I've tweaked it in a few place for my purposes, but one may help everyone. I couldn't get close to work. I looked at the function and noticed two things about the _ExcelBookClose Application was after $oExcel and DisplayAlerts = true and ScreenUpdating = true were before quit which was causing it to display the alerts before quiting (just backwards). Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $fSave > 1 Then $fSave = 1 If $fSave < 0 Then $fSave = 0 If $fAlerts > 1 Then $fAlerts = 1 If $fAlerts < 0 Then $fAlerts = 0 $oExcel.DisplayAlerts = $fAlerts $oExcel.ScreenUpdating = $fAlerts If $fSave Then $oExcel.ActiveWorkBook.Save EndIf $oExcel.Quit $oExcel.DisplayAlerts = True $oExcel.ScreenUpdating = True Return 1 EndFunc ;==>_ExcelBookClose BTW, I took .Application out because I didn't understand why it was there. There may be a very good reason. I just couldn't find any clues on MSDN. Edited March 21, 2007 by JohnBailey A decision is a powerful thing
JohnBailey Posted March 21, 2007 Posted March 21, 2007 Three new parameter additions to consider: UpdateLinks Security fpassword expandcollapse popup;=============================================================================== ; ; Description: Opens an existing workbook and returns its object identifier. ; Syntax: $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1) ; Parameter(s): sFilePath - Path and filename of the file to be opened ; $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1) ; $fReadOnly - Flag, whether to open the workbook as read-only (True or False) (default=False) ; $security - Flag, whether to run macros, deny macros, or ask (1 = allow, 2 = ask, 3 = deny) (default=2) ; $updateLinks - Flag, whether to ask for updatelinks or automatically update (1 = ask, 2 = automatically update, 0 = no update) (default=1) ; $fpassword - String, the password required to open a password protected excel file (default='') ; Requirement(s): None ; Return Value(s): On Success - Returns new object identifier ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - File does not exist ; Author(s): SEO <locodarwin at yahoo dot com> ; Editor(s): JohnBailey <john at schnitmangroup dot com> and cameronsdad <http://www.autoitscript.com/forum/index.php?act=Msg&CODE=4&MID=6554> ; Note(s): I (John) recommend setting AutomationSecurity back to the user's default when finished. ; ;=============================================================================== Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $security = 2,$updateLinks = 1, $fpassword = '') $oExcel = ObjCreate("Excel.Application") If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 If $fReadOnly = 1 Then $fReadOnly = True If $fReadOnly = 0 Then $fReadOnly = False If $updateLinks = 1 Then $updateLinks = True If $updateLinks = 0 Then $updateLinks = True If $updateLinks = 2 Then $updateLinks = False With $oExcel .Visible = $fVisible .AutomationSecurity = $security .AskToUpdateLinks = $updateLinks If $fVisible = 0 AND $security = 1 Then .ScreenUpdating = False EndIf If $updateLinks = 1 or 2 Then .WorkBooks.Open($sFilePath, True, $fReadOnly, Default, $fpassword) Else .WorkBooks.Open($sFilePath, False, $fReadOnly, Default, $fpassword) EndIf .ActiveWorkbook.Sheets(1).Select() .AskToUpdateLinks = true EndWith Return $oExcel EndFunc ;==>_ExcelBookOpen locoDarwin, you can remove my name from the editor(s) area. I don't care, but I do give mad props to cameronsdad. A decision is a powerful thing
L01c Posted March 22, 2007 Posted March 22, 2007 Yes, unfortunately I can't predict how my collection will behave on non-English installations. Sorry about that. Since your code change takes into account both German and English installations, I'll use it. That might be the only function that requires such a change, since in other situations I merely look for numbers vs. letters and not a particular letter. Thanks for the submission! -S ... Except that it now probably doesn't work properly with other languages where localized "Column" starts with C... As in French & Spanish. Got that from here: Language Cell Item Name English R1C1 French L1C1 Dutch R1K1 Spanish F1C1 German Z1S1
DjDeep00 Posted April 14, 2007 Posted April 14, 2007 How can I read the entire excel file into one array? I tried creating my own array by placing the _ExcelReadArray() into a for loop. But I need to know how many rows and columns the excel file has. Maybe someone already has done this? Please help.
DjDeep00 Posted April 16, 2007 Posted April 16, 2007 This is what I have but not sure if this is the best way? $Excel_File=_ExcelBookOpen(@ScriptDir & "\Billing.xls",0) $i=0 For $x=65 to 90 $i=$i+1 $Data=_ExcelReadCell($Excel_File,Chr($x) & "1",1) If $x>65 and $Data="" then For $j=1 to 5000 $Data=_ExcelReadCell($Excel_File,"A" & $j,1) If $Data="" then ExitLoop Next ExitLoop EndIf Next Dim $New_Excel_Array[1] For $y=1 to $j-1 $Excel_Array=_ExcelReadArray($Excel_File, $y, 1, $i-1, 0,1) _ArrayAdd($New_Excel_Array,StringReplace(_ArrayToString($Excel_Array,"|"),$i-1 & "|","")) Next _ArrayDisplay($New_Excel_Array,"") _ExcelBookClose($Excel_File,0)
mikeytown2 Posted April 16, 2007 Posted April 16, 2007 (edited) I would use this function to get the used range of the excel worksheet ;=============================================================================== ; ; Description: Return the last cell of the used range in the specified worksheet. ; Syntax: $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $vSheet - The sheet name or number to be checked. ; Requirement(s): None ; Return Value(s): On Success - Returns an array of used range values: ; $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank) ; $array[1] - The last cell used, in R1C1 format ; $array[2] - The last column used, as an integer ; $array[3] - The last row used, as an integer ; On Failure - Returns 0 (as non-array numeric value) and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Invalid sheet number ; @error=3 - Invalid sheet name ; Author(s): DaLiMan, SEO <locodarwin at yahoo dot com> ; Note(s): Upon return, $array[0] will equal numeric value 0 if the worksheet is blank ; ;=============================================================================== Func _ExcelSheetUsedRangeGet($oExcel, $vSheet) And if you want to grab all the info from all the worksheets i would use this function to list the worksheets ;=============================================================================== ; ; Description: Return a list of all sheets in workbook, by name, as an array. ; Syntax: _ExcelSheetList($oExcel) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; Requirement(s): None ; Return Value(s): On Success - Returns an array of the sheet names in the workbook (the zero index stores the sheet count) ; 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): None ; ;=============================================================================== Func _ExcelSheetList($oExcel) Then use this to activate the worksheet that was found ;=============================================================================== ; ; Description: Activate the specified sheet by string name or by number. ; Syntax: _ExcelSheetActivate($oExcel, $vSheet) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $vSheet - The sheet to activate, either by string name or by number ; 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 ; @error=2 - Specified sheet number does not exist ; @error=3 - Specified sheet name does not exist ; Author(s): SEO <locodarwin at yahoo dot com> ; Note(s): None ; ;=============================================================================== Func _ExcelSheetActivate($oExcel, $vSheet) Then using all of the data that was gathered from above, i would use the excel read array function ;=============================================================================== ; ; Description: Create an array from a row or column of the active worksheet. ; Syntax: _ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0) ; Parameter(s): $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $iStartRow - The table row to start reading the array from ; $iStartColumn - The table column to start reading the array from ; $iNumCells - The number of cells to read into the array ; $iDirection - The direction of the cells to read into array (0=right, 1=down) ; $fIndexBase - Specify whether array created is to have index base of either 0 or 1 ; Requirement(s): None ; Return Value(s): On Success - Returns an array with the specified cell contents ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Parameter out of range ; @extended=0 - Row out of range ; @extended=1 - Column out of range ; @error=3 - Invalid number of cells ; @error=4 - Invalid direction parameter ; Author(s): SEO <locodarwin at yahoo dot com> ; Note(s): None ; ;=============================================================================== Func _ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0) One thing to note about this is it only reads 1 row or one Column, as stated in the $iDirection variable. so you need to use this multiple times if you have more then 1 row or column of data.EDITAlmost forgot the ExcelSheetUsedRangeGet function requires the name of the excel sheet, i would use this function to get that. ;=============================================================================== ; ; Description: Return the name of the active sheet. ; Syntax: $string = _ExcelSheetNameGet($oExcel) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; Requirement(s): None ; Return Value(s): On Success - Returns the name of the active sheet (string) ; 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): None ; ;=============================================================================== Func _ExcelSheetNameGet($oExcel)Also this code will require multiple loops, otherwise it won't work too well. Edited April 16, 2007 by mikeytown2 Email: POP3 & SMTP using SSL/TLS (OpenSSL)Email: IMAPEmail: Base64 & SMTP login & Send email direct to MX Server (thanks blindwig)Win: Hook Registry ChangesWin: Read/Write to Alternate Data Streams (ini example)Utility: GPS Distance Calculations, Vincenty and Haversine formulas; angles and elevationUtility: Dell Laser Printer (3000-5100) - Print LoggerUtility: Reset Router when Wireless Link FailsUtility: ImageMagick Batch jpg ProcessorVideo HCenc Batch FrontendVideo: *DEAD* YouTube Video Encoder (avs/avi to flv)Software On CD's I Like<<back|track3 Ultimate Boot CD for Windows SpinRite Ubuntu ophcrack
randallc Posted April 16, 2007 Posted April 16, 2007 Hi, If you don't want to do looping, see my array read func in ExcelCom (link in my sig) I doubt the new Locodarwin function needs to loop, actually, but check with him re syntax? Berst, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Locodarwin Posted April 16, 2007 Author Posted April 16, 2007 I apologize for not getting back to you sooner. Mikeytown's solution is recommended. Randallc is right, in that you do not need to loop the _ExcelReadArray() function. However, the function only supports one dimension of data reading, so if you have a table (as opposed to a list) then you'll need to perform one _ExcelReadArray() for each additional row or column to be read. At some point in the future, the function will likely be expanded to include 2-dimensional array capture, but for now it does not. If Randallc's function supports 2-dimensions (he'd have to chime in for confirmation), and that's what you need, then by all means put it to good use. L01c: I may someday expand the associated functions to support multiple languages; as it stands, I can't really justify spending the time on it other than on a case by case basis. Thanks for your input. -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]
randallc Posted April 16, 2007 Posted April 16, 2007 If Randallc's function supports 2-dimensions (he'd have to chime in for confirmation),Hi,Yes, the examples with arrays show 2d funcs; examples show use with "usedrange" as well; one call only,m so fairly fast and easyBest, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
EasyGoing Posted April 25, 2007 Posted April 25, 2007 My statement works if I change the variable to a numberic value. The issue is in the string or format that the clipboard is in. IF i change the "test" to 1 and run the script is works. What do you thing it is? Everything else works. copy paste variable all works. It just will not read the char type or string type. WinActivate("Microsoft Excel - question.xls") WinWaitActive("Microsoft Excel - question.xls") Send("^c") Sleep(100) Dim $occupation $occupation = ClipGet() If $occupation = "test" Then Send("{DOWN}") Sleep(1000) send($occupation) Else Send("{DOWN}") Sleep(1000) Send($occupation) Send("did not work") EndIf
gcriaco Posted April 30, 2007 Posted April 30, 2007 (edited) Minor UDF typo in ExcelCOM_UDF.au3, _ExcelBookClose function header:; Syntax: _ExcelCloseDoc($oExcel, $fSave = 1, $fAlerts = 0) => _ExcelBookClose ($oExcel, $fSave = 1, $fAlerts = 0) Sorry for bothering you and many thanks for your JobPeppeP.S.: what about the inclusion of your UDF in the Scite4AutoIt package?I read the roadmap. BTW, hope you'll finish the UDF ASAP. Thanks again. Edited April 30, 2007 by gcriaco
livewire Posted May 9, 2007 Posted May 9, 2007 Are these lines supposed to be in _ExcelBookClose()? $oExcel.Application.DisplayAlerts = True $oExcel.Application.ScreenUpdating = True I think they should be removed -Livewire
Leykis101 Posted May 10, 2007 Posted May 10, 2007 I love your Excel UDF, except one thing. No matter what I do, I cannot get it to sort. I am hoping you can help me with this. Below is the code I have tried, as you can see I tried two different ways to sort it. I am using Excel 2007, but I can try it on 2003 if that is the problem. I have tried sorting before on 03 though and could never get it to work. I have also included links to the 2 .xls files im trying to get to sort. It is nothing but a bunch of numbers, but thats what I need to do in the end. Please help me if you can. ThanksTest Excel File.xlsTest Excel File2.xlsCODE#NoTrayIcon#include <_ExcelCOM_UDF.au3>#include <Array.au3>Dim $oExcel1, $oExcel2, $fVisible, $fSave, $fAlerts, $vSheet, $aTemp, $errorret$oExcel1 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File.xls", $fVisible = 1)$oExcel2 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File2.xls", $fVisible = 1)_ExcelShow($oExcel1)_ExcelShow($oExcel2)$aTemp1 = _ExcelSheetUsedRangeGet($oExcel1, 1)$aTemp2 = _ExcelSheetUsedRangeGet($oExcel2, 1)_ExcelCopy($oExcel1, 1, 1, $aTemp1[3], $aTemp1[2])_ExcelPaste($oExcel2, $aTemp2[3]+1, 1)Sleep ( 1000 )$errorret = _ExcelSortExtended($oExcel2, "A1:Z999", "A1", 1, $xlYes, False, $xlSortRows, $xlSortNormal);~ _ExcelSort($oExcel2, 1, 1, 1, 10, 10)MsgBox ( 0, "", $errorret & " " & @error & " " & @extended )
livewire Posted May 10, 2007 Posted May 10, 2007 I love your Excel UDF, except one thing. No matter what I do, I cannot get it to sort. I am hoping you can help me with this. Below is the code I have tried, as you can see I tried two different ways to sort it. I am using Excel 2007, but I can try it on 2003 if that is the problem. I have tried sorting before on 03 though and could never get it to work. I have also included links to the 2 .xls files im trying to get to sort. It is nothing but a bunch of numbers, but thats what I need to do in the end. Please help me if you can. Thanks Test Excel File.xls Test Excel File2.xls CODE#NoTrayIcon #include <_ExcelCOM_UDF.au3> #include <Array.au3> Dim $oExcel1, $oExcel2, $fVisible, $fSave, $fAlerts, $vSheet, $aTemp, $errorret $oExcel1 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File.xls", $fVisible = 1) $oExcel2 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File2.xls", $fVisible = 1) _ExcelShow($oExcel1) _ExcelShow($oExcel2) $aTemp1 = _ExcelSheetUsedRangeGet($oExcel1, 1) $aTemp2 = _ExcelSheetUsedRangeGet($oExcel2, 1) _ExcelCopy($oExcel1, 1, 1, $aTemp1[3], $aTemp1[2]) _ExcelPaste($oExcel2, $aTemp2[3]+1, 1) Sleep ( 1000 ) $errorret = _ExcelSortExtended($oExcel2, "A1:Z999", "A1", 1, $xlYes, False, $xlSortRows, $xlSortNormal) ;~ _ExcelSort($oExcel2, 1, 1, 1, 10, 10) MsgBox ( 0, "", $errorret & " " & @error & " " & @extended ) It's key first then range. $errorret = _ExcelSortExtended($oExcel2, "A1", "A1:Z999", 1, $xlYes, False, $xlSortRows, $xlSortNormal)
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