Excel Rewrite usage

[redrum]

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

[kylomas]

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

[redrum]

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?

[BrewManNH]

Post your code and we'll have a better understanding of what's going on.

[redrum]

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

[redrum]

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.

[water]

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.

[redrum]

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.

[redrum]

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

[water]

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.

[redrum]

Perfect, thanks for the rapid response.  Staying with the original UDF will save me time for now.

[water]

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.

[redrum]

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

[water]

 

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?

[redrum]

 

 

 

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

[water]

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)

[redrum]

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

[water]

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.