Jump to content
Sign in to follow this  
redrum

Excel Rewrite usage

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
BrewManNH

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 Gude
How 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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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:

;===============================================================================
;
; 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
redrum

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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  

×