Jump to content

Excel question


Recommended Posts

Hi, I am trying to sort a column in excel and it is giving me fits. I suspect my parameters are at fault but I dont know enough about using UDFs to say for sure. I get the same error if I am using _ExcelSort or _ExcelSortExtended.

CODE

#include <ExcelCom.au3>

$path = @ScriptDir & "\worksheet.xls"

$open = _ExcelBookOpen($path, 0)

_ExcelSort($open, 1, 1, 1, 10, 1)

_ExcelBookClose($open)

Any and all help is appreciated

Link to comment
Share on other sites

Hi, I am trying to sort a column in excel and it is giving me fits. I suspect my parameters are at fault but I dont know enough about using UDFs to say for sure. I get the same error if I am using _ExcelSort or _ExcelSortExtended.

Any and all help is appreciated

I believe if you check the topic where randallc posted the ExcelCOM.au3 UDF, you will notice he hasn't updated it since last October, and he recommends Locodarwin's ExcelCOM_UDF.au3 instead. The sort function from Locodarwin looks like this:

;===============================================================================
;
; 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

You might want to try it with the more current UDF.

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

I believe if you check the topic where randallc posted the ExcelCOM.au3 UDF, you will notice he hasn't updated it since last October, and he recommends Locodarwin's ExcelCOM_UDF.au3 instead. The sort function from Locodarwin looks like this:

;===============================================================================
;
; 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

You might want to try it with the more current UDF.

:)

Ahhh but PsaltyDS we have had this conversation before :P I am using Locodarwins most recent offering

CODE

Title: Microsoft Excel COM UDF library for AutoIt v3

Author(s): SEO aka Locodarwin; contributions from DaLiMan, Stanley Lim

Date Began: 06-12-06

Current Ver: 1.32

Last Update: 02-12-07

And the function that that you recommend is the one that I am using. I get the following error.

Line 984 (File "C:\Program Files\AutoIt3\Include\ExcelCom.au3"):

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR

Error: The requested action with this object has failed.

Any advice?

Link to comment
Share on other sites

Ahhh but PsaltyDS we have had this conversation before :)

Don't expect old people to remember what happened before their last breakfast... :)

I am using Locodarwins most recent offering

CODE

Title: Microsoft Excel COM UDF library for AutoIt v3

Author(s): SEO aka Locodarwin; contributions from DaLiMan, Stanley Lim

Date Began: 06-12-06

Current Ver: 1.32

Last Update: 02-12-07

Fooled me with the file name in your #include. I have randallc's version saved as ExcelCOM.au3, and Locodarwin's as ExcelCOM_UDF.au3.

And the function that that you recommend is the one that I am using. I get the following error.

Line 984 (File "C:\Program Files\AutoIt3\Include\ExcelCom.au3"):

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey)^ ERROR

Error: The requested action with this object has failed.

Any advice?

First, quit getting advice from people who know as little about it as I do.

Second, if you ignore the first, maybe this rewrite of that all-in-one line of code will tell you which part it doesn't like: getting the sorted range, getting the key range, or doing the sort:

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)
        $oStartCell = $oExcel.Cells($sRangeOrRowStart, $iColStart)
        $oEndCell = $oExcel.Cells($iRowEnd, $iColEnd)
        $oSortRange = $oExcel.Range($oStartCell, $oEndCell)
        $oKeyRange = $oExcel.Range($sKey)
        $oSortRange.Sort($oKeyRange, $iDirection)
    Else
        $oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection)
    EndIf
    Return 1
EndFunc ;==>_ExcelSort

This is meant to be the exact same function, just broken into discreet steps. It should give you the same error, but it would be telling to see exactly which line got the error.

:P

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Don't expect old people to remember what happened before their last breakfast... :P

Fooled me with the file name in your #include. I have randallc's version saved as ExcelCOM.au3, and Locodarwin's as ExcelCOM_UDF.au3.

First, quit getting advice from people who know as little about it as I do.

Second, if you ignore the first, maybe this rewrite of that all-in-one line of code will tell you which part it doesn't like: getting the sorted range, getting the key range, or doing the sort:

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)
        $oStartCell = $oExcel.Cells($sRangeOrRowStart, $iColStart)
        $oEndCell = $oExcel.Cells($iRowEnd, $iColEnd)
        $oSortRange = $oExcel.Range($oStartCell, $oEndCell)
        $oKeyRange = $oExcel.Range($sKey)
        $oSortRange.Sort($oKeyRange, $iDirection)
    Else
        $oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection)
    EndIf
    Return 1
EndFunc ;==>_ExcelSort

This is meant to be the exact same function, just broken into discreet steps. It should give you the same error, but it would be telling to see exactly which line got the error.

:)

If I read this right it looks like the sort is where it fails at. (I commented out the original function and plugged in the rewrite)

Line 1001 (File "C:\Program Files\AutoIt3\Include\ExcelCom.au3"):

$oKeyRange = $oExcel.Range($sKey)

$oKeyRange = $oExcel.Range($sKey)^ ERROR

Error: The requested action with this object has failed.

I looked at the Sort Method reference on MSDN but nothing jumped out at me that might cause this (that of course is not a huge statement as my comprehension of VBA is limited).

Link to comment
Share on other sites

Try this:

_ExcelSort($open, "A1", 1, 1, 10, 1)
Yeah, running my hacked version of the sort, you see it doesn't like the $sKey parameter:

>Running:(3.2.4.9):C:\Program Files\AutoIt3\autoit3.exe "C:\Scripts\Test\Test2.au3" 
C:\Scripts\Test\Test2.au3 (20) : ==> The requested action with this object has failed.: 
$oKeyRange = $oExcel.Range ($sKey) 
$oKeyRange = $oExcel.Range ($sKey)^ ERROR

I was trying to sort a three column sheet on column B for testing what I posted. It wouldn't take 1, or "1", or "B", but it did take "B1".

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Yeah, running my hacked version of the sort, you see it doesn't like the $sKey parameter:

>Running:(3.2.4.9):C:\Program Files\AutoIt3\autoit3.exe "C:\Scripts\Test\Test2.au3" 
C:\Scripts\Test\Test2.au3 (20) : ==> The requested action with this object has failed.: 
$oKeyRange = $oExcel.Range ($sKey) 
$oKeyRange = $oExcel.Range ($sKey)^ ERROR

I was trying to sort a three column sheet on column B for testing what I posted. It wouldn't take 1, or "1", or "B", but it did take "B1".

:)

That did it! It took me a while to get the relationships in that string "B1", 1, 2, 10, 2 to sort just the B column (I had been staring all day so it was getting blurry). Thanks a ton guys!
Link to comment
Share on other sites

Thanks, guys, for providing help to lilruly. This one has come up a couple of times. On my soon-to-be uploaded update to the UDF, I've included some comments for this function explaining that the sort key must be a full cell reference, not just a column or row. VBA will allow a row or column designation, but for some reason COM calls require a cell.

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

  • 6 months later...

Hi Locodarwin, I love you sig... that has always been one of my favorite quotes. I am also having a problem with _excelsort() from your excel UDF (v1.32). I am seeing this:

C:\Program Files\AutoIt3\Include\ExcelCOM_UDF.au3 (988) : ==> The requested action with this object has failed.:

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)

$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort ($oExcel.Range($sKey), $iDirection)^ ERROR

So I used your modification to break down the function call and it says the error occured at:

$oSortRange.Sort($oKeyRange, $iDirection)

$oSortRange.Sort($oKeyRange, $iDirection)^ ERROR

I am using the your sort function to create a type of explorer type sort, where you click on the heading at it sorts the entries by that field.

$iRange = _ExcelSheetUsedRangeGet($oExcel, "Statistics")
$iRowGraphLink = $iRange[3]
Do
        $iExcelColumn = $oExcel.ActiveCell().Column
        $iExcelRow = $oExcel.ActiveCell().Row
        If $iExcelRow = $iRowGraphLink - 1 And $iExcelColumn = 1 Then
            $oExcel.ActiveSheet.Cells($iRowGraphLink - 1, 1).Select;need change selection before coming back to 'Statistics'
            _ExcelSheetActivate($oExcel, "Graph")
        ElseIf $iExcelRow = 1 And $iExcelColumn = 1 Then
            _ExcelSort($oExcel, "A1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 2 Then 
            _ExcelSort($oExcel, "B1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 3 Then 
            _ExcelSort($oExcel, "C1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 4 Then
            _ExcelSort($oExcel, "D1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 5 Then
            _ExcelSort($oExcel, "E1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 6 Then
            _ExcelSort($oExcel, "F1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 7 Then
            _ExcelSort($oExcel, "G1", 2, 1, $iRowGraphLink - 2, 8, 1)
        ElseIf $iExcelRow = 1 And $iExcelColumn = 8 Then
            _ExcelSort($oExcel, "H1", 2, 1, $iRowGraphLink - 2, 8, 1)
        EndIf
    EndIf
    Sleep(500)
Until 0 = 1

The problem is that it does not fail consistently. Sometimes it fails one the first sort, sometimes on the fifth, sometimes on the eleventh. Have you any ideas what could be going wrong?

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...