redrum Posted August 21, 2013 Share Posted August 21, 2013 Hello, I need to sort a range on an excel sheet and have not been able to do it with the standard UDF - if there is a way please point me to some help. So, I downloaded the Excel Rewrite. Problem: I can't get the #Include <Excel Rewrite.au3> to work. I am getting error:can't open include file < .. .> I'm not sure where to install the files so they will be included. Any help appreciated! Thanks Link to comment Share on other sites More sharing options...
kylomas Posted August 21, 2013 Share Posted August 21, 2013 (edited) The include is excel.au3 (#include <excel.au3>). I don't know what "rewrite" is but suspect that it is a function within excel.au3. As such it can be invoked in code as rewrite(any parms required). kylomas edit: Are you using the full verison of SCiTE? Sorry, reading with both eyes closed. See the Help file #include for a description of the search order for includes. One thing not mentioned in the Help file is a Tool called SCITECONFIG in the full version of SCiTE. Using this (Tools|ScIte Config) you can name a user level include library. Also, the name of the folder is "Excel Rewrite Beta 1". Edited August 21, 2013 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 I copied the Excel Rewrite downloaded files - Excel Rewrite.au3 and ExcelConstants - into the AutoItInclude folder. Still, I get an error from the Include statements. I'm using Beta version v3.3.9.4 Not sure what to do next? Link to comment Share on other sites More sharing options...
BrewManNH Posted August 21, 2013 Share Posted August 21, 2013 Post your code and we'll have a better understanding of what's going on. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 Just trying to get the #include statements to work for the Excel Rewrite, so that I can use some of the new Excel UDFs. Thanks Excel Rewrite test.au3 Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 I get an error on both the #include statements - Excel Rewrite.au3 and ExcelConstants.au3. Not sure if I have all the correct files moved to the AutoItinclude folder. Link to comment Share on other sites More sharing options...
water Posted August 21, 2013 Share Posted August 21, 2013 In your test script you try to use "Excel.au3" and "Excel Rewrite.au3". This doesn't work. You can't use both UDFs at the same time. Place the Excel Rewrite.au3 and ExcelConstants.au3 into the directory where your script is located. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 OK thanks, Water. Includes now working. I see that some of the Excel statements in my scripts are now giving me undefined function errors - I think I just now need to go in and fix the ones with errors to the new syntax in the rewrite UDF. Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 Considering switching to new Excel Rewrite UDF - trying do decide - I think I understand the following: -Excel Rewrite UDF only works in Beta versions -Cannot Use both Rewrite and original UDF both - so some statements need to be changed to align with new versions in Rewrite UDF Question: The reason I started this was because I need to sort ranges in an Excel sheet. I tried statements of the following form: $oExcel.Worksheets("DATA").Range(Cells($FirstRow, 1).Address, Cells($LastRow, $PositionCol).Address).Sort Key1:=Range(Cells($FirstRow, $EndCol).Address), Order1:=xlAscending The above does not seem to work (it could be I have some other type or error, however). Should a sort statement such as this work with the original Excel UDF? If not, is there a method of sorting an Excel sheet available in the original Excel UDF? If possible I would like to stay with the original UDF Advice would be much appreciated. Thanks Link to comment Share on other sites More sharing options...
water Posted August 21, 2013 Share Posted August 21, 2013 Considering switching to new Excel Rewrite UDF - trying do decide - I think I understand the following: -Excel Rewrite UDF only works in Beta versions Correct. But it isn't too complex to make it work with AutoIt 3.3.8.1. Remove function _Excel_BookAttach (if you don't need it) and replace the ternary operators with If/Then/Else/EndIf statements (I can post the correct code if needed). -Cannot Use both Rewrite and original UDF both - so some statements need to be changed to align with new versions in Rewrite UDF Function names are unique but the most of the constants are not and hence you will see a lot off error messages. It's easier to make the Excel rewrite work with 3.3.8.1. Question: The reason I started this was because I need to sort ranges in an Excel sheet. I tried statements of the following form: $oExcel.Worksheets("DATA").Range(Cells($FirstRow, 1).Address, Cells($LastRow, $PositionCol).Address).Sort Key1:=Range(Cells($FirstRow, $EndCol).Address), Order1:=xlAscending The above does not seem to work (it could be I have some other type or error, however). Should a sort statement such as this work with the original Excel UDF? If not, is there a method of sorting an Excel sheet available in the original Excel UDF? You can't specify parameters this way: "Order1:=xlAscending". AutoIt doesn't support parameters by name. You can either grab function _Excel_RangeSort from the new Excel UDF and add it to your script or a separate UDF. This way you can stay with the original Excel UDF. Or use the following function to sort a range by a single key: expandcollapse popup;=============================================================================== ; ; Description: Performs a simplified sort on a range. ; Syntax: _ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sKey - The key column or row to sort by (a letter for column, a number for row) ; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ; $iColStart - The starting column for the number format(left) (default=1) ; $iRowEnd - The ending row for the number format (bottom) (default=1) ; $iColEnd - The ending column for the number format (right) (default=1) ; $iDirection - Sort direction (1=Ascending, 2=Descending) (default=descending) ; 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 - Starting row or column invalid ; @extended=0 - Starting row invalid ; @extended=1 - Starting column invalid ; @error=3 - Ending row or column invalid ; @extended=0 - Ending row invalid ; @extended=1 - Ending column invalid ; Author(s): SEO <locodarwin at yahoo dot com>, many thanks to DaLiMan ; Note(s): This sort routine will not function properly with pivot tables. Please ; use the pivot table sorting functions instead. ; ;=============================================================================== Func _ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0) If $iColStart < 1 Then Return SetError(2, 1, 0) If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0) If $iColEnd < $iColStart Then Return SetError(3, 1, 0) $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort _ ($oExcel.Range($sKey), $iDirection) Else $oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection) EndIf Return 1 EndFunc ;==>_ExcelSort This is taken from >ExcelCOM_UDF. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 21, 2013 Author Share Posted August 21, 2013 Perfect, thanks for the rapid response. Staying with the original UDF will save me time for now. Link to comment Share on other sites More sharing options...
water Posted August 21, 2013 Share Posted August 21, 2013 If you find some spare time I would be glad if you could test the new Excel UDF Play with the example scripts and tell me what you think, how it works, what is missing etc. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 22, 2013 Author Share Posted August 22, 2013 Hello again, I've been trying to get the sort function from the COM function you provided above to work. Everything executes and my parameters are passed correctly to the function. This statement (in the sort routine) executes, returns with no error, but does not sort my sheet: $oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection) Question: I do not understand this statement - It looks like a VBA sort statement that I am not familiar with, without the KeyWords for Key, order, etc.? I also tried to use the sort routine from Excel Rewrite and am having some trouble with that also - not sure how to define a range variable $vRange. My range to sort is "variable" as the number of rows to be sorted changes within the script. So I cannot use a constant. Anyway, I'm learning a lot with this exercise, even though I have yet to get a successful sort! I would like to switch to the new Excel UDF and will definitely find some time to work on that on the side. I have too many scripts working with the original right now and really need to get this sort at least working first. Need a little direction! Best regards, Doug Link to comment Share on other sites More sharing options...
water Posted August 22, 2013 Share Posted August 22, 2013 Question: I do not understand this statement - It looks like a VBA sort statement that I am not familiar with, without the KeyWords for Key, order, etc.? AutoIt does not support passing parameters by name like VBS does. So the parameters have to be passed in the sequenece tehy are expected by the Excel method. How do you call the sort function from the COM UDF? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 22, 2013 Author Share Posted August 22, 2013 I used these testing cases to call the COM UDF sort function: ; $sKeyColLetter = _Excel_ColumnToLetter($PositionCol+1) ;Used _Excel_ColumnToLetter function from same COM UDF $rv=_ExcelSort($oExcel, $sKeyColLetter, $FirstTickerRow, 1, $LastTickerRow, $PositionCol+1, 1) ConsoleWrite("Ret Value, Error = " & $rv & @Error & @CRLF) ;no sort occured, "success and no errors" returned ;NOTE: I tried these also for test cases ;$rv=_ExcelSort($oExcel, $SrtKey, $SrtRange, 1, 1, 1, 1) ;Not sure how to define SrtRange! ;$rv=_ExcelSort($oExcel, "AH", "A4:AH90", 1, 1, 1, 1) ;So I tried a constant for sort range - no sort occured, "success and no errors" returned Link to comment Share on other sites More sharing options...
water Posted August 22, 2013 Share Posted August 22, 2013 You can only sort on a single column with _ExcelSort. Should be something like this (untested): _ExcelSort($oExcel, "A:A", "A4:AH90", 1, 1, 1, 1) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
redrum Posted August 23, 2013 Author Share Posted August 23, 2013 Thanks Water, Understanding the need to put the sort key in the "X:X" format (from your last post) got me a successful sort - I'm now on a path to finish this script. I had to calculate the key-range and sort-range in the string format as follows: ;SORT-RANGE COMPUTATION IN A1 FORMAT: $StrtColLetter="A" $StrtRow=$FirstTickerRow $EndCol=$PositionCol+1 $EndColLetter=_Excel_ColumnToLetter($EndCol) $EndRow=$LastTickerRow $sSortRange=String($StrtColLetter&$StrtRow&':'&$EndColLetter&$EndRow) ConsoleWrite("Calculated Sort Range = " & $sSortRange & @CRLF) ;KEY-RANGE COMPUTATION ("X:X") $SortCol=$PositionCol+1 ;$SortCol=1 $sKeyColLetter = _Excel_ColumnToLetter($SortCol) $sKeyRange=String($sKeyColLetter&':'&$sKeyColLetter) ConsoleWrite("Calculated Key Range = " & $sKeyRange & @CRLF) ;NOTE! Key needs to be a range of form "A:A", not just the column letter as documentation indicates! $rv=_ExcelSort($oExcel, $sKeyRange, $sSortRange, 1, 1, 1, 1) ConsoleWrite("Ret Value, Error = " & $rv & @Error & @CRLF) This is now working. Final question - just for my knowledge: I'm using a string for the ranges here. The documentation in _ExcelSort indicates "Range Object" can be used also. I'm just wondering if there is another way to define the ranges using row/column numbers directly so doing the string calculation to create the "X:X" format would not be required. - - similiar to the (cells(r,c).address, cells(r,c).address) in VBA code? Your help is greatly appreciated I plan to try out a switch to the Excel Rewrite UDF soon - would like to use with production AutoIt version. I'll likely need some direction on that later. Thanks Link to comment Share on other sites More sharing options...
water Posted August 23, 2013 Share Posted August 23, 2013 Taking this statement from the _ExcelSort function $oRange = $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort _ ($oExcel.Range($sKey), $iDirection) sets the range. You specify starting and ending row and column. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
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