Excel question

[GMK]

Assuming $oExcel is a valid object, what's wrong with this line?

$oExcel.Range ($oExcel.ActiveSheet.UsedRange).Sort ($oExcel.Range ("B2"), 1, $oExcel.Range ("C2"), 1, $oExcel.Range ("A2"), 1, 0, 1, False, 1, 0, 0, 0)

C:\Documents and Settings\...\My Documents\file.au3 (225) : ==> The requested action with this object has failed.: 
.Range ($oExcel.ActiveSheet.UsedRange).Sort ($oExcel.Range ("B2"), 1, $oExcel.Range ("C2"), 1, $oExcel.Range ("A2"), 1, 0, 1, False, 1, 0, 0, 0) 
.Range ($oExcel.ActiveSheet.UsedRange)^ ERROR

(I used With $oExcel for that particular command, in case anyone is curious.)

[Locodarwin]

Howdy do!

Your problem is possibly the UsedRange property. It's buggy. Try getting your range a different way; perhaps find it using a function like this one, written by DaLiMan and myself:

expandcollapse popup

;===============================================================================
;
; 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)
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If IsNumber($vSheet) Then
        If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
    Else
        $aSheetList = _ExcelSheetList($oExcel)
        For $xx = 1 To $aSheetList[0]
            If $aSheetList[$xx] = $vSheet Then $fFound = 1
        Next
        If NOT $fFound Then Return SetError(3, 0, 0)
    EndIf
    $oExcel.ActiveWorkbook.Sheets($vSheet).Select
    $aSendBack[0] = $oExcel.Application.Selection.SpecialCells(11).Address
    $aSendBack[1] = $oExcel.Application.Selection.SpecialCells(11).Address(True, True, $xlR1C1)
    $aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $sTemp = StringSplit($aSendBack[1], "C")
    $aSendBack[2] = Number($sTemp[2])
    $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "[^0-9]", ""))
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc ;==>_ExcelSheetUsedRangeGet

Good luck!

-S

Edited May 10, 2007 by Locodarwin

[GMK]

I've been using the ExcelCOM UDF (thanks, btw ) and didn't notice that function. Here's the latest:

$usedrange = _ExcelSheetUsedRangeGet($oExcel, $company[$i] & " " & $source)
$oExcel.Range("A1:" & $usedrange[0]).Sort ("B2", 1, "C2", 1, "A2", 1, 0, 1, False, 1, 0, 0, 0)oÝ÷ ÙÊx.|Óݡ˦z{ljwRzÛb4÷i­r
ÞOv3 èréÛ4÷bÈLEjíøÓm½ã^|

Here is the original VBA, which worked fine:

Range("A1:F" & ActiveSheet.UsedRange.Rows.Count).Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range _
        ("C2"), Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal

[Locodarwin]

Howdy do,

Well, at least you made it past UsedRange. Your problem now is entirely different. When translating VBA into AutoIt COM, you have to remember that you can't skip parameters in the call. You have to use the "Default" keyword for any parameters you won't be using if you intend to use parameters that come after the non-used ones. You're missing two parameters in the offending line, which is almost certainly causing your error.

The parameters for the .Sort() method should be as follows:

.Sort([Key1]), [Order1], [Key2], [Type], [Order2], [Key3], [Order3], [Header], [OrderCustom], [MatchCase], [Orientation], [sortMethod], [DataOption1], [DataOption2], [DataOption3])

It looks like you're skipping the [Type] and [sortMethod] parameters. Try your call again like this:

$oExcel.Range("A1:" & $usedrange[0]).Sort ("B2", 1, "C2", Default, 1, "A2", 1, 0, 1, False, 1, Default, 0, 0, 0)

I don't have Excel right this moment to test it out. Let me know, and good luck with your Excel endeavors!

-S

[GMK]

Thanks, but it still caused the same error. I never knew it would be so difficult to sort on multiple keys.

C:\Documents and Settings\...\My Documents\file.au3 (228) : ==> The requested action with this object has failed.: 
$oExcel.Range("A1:" & $usedrange[0]).Sort ("B2", 1, "C2", Default, 1, "A2", 1, 0, 1, False, 1, Default, 0, 0, 0) 
$oExcel.Range("A1:" & $usedrange[0]).Sort ("B2", 1, "C2", Default, 1, "A2", 1, 0, 1, False, 1, Default, 0, 0, 0)^ ERROR

[Locodarwin]

Okay, think I know what's missing. I really disliked coding the _ExcelSortExtended() function in the UDF for this very reason. Keys need to be range objects in COM. In your VBA example, you are indeed using a range object. Here's how I think your function will need to look:

$oExcel.Range("A1:" & $usedrange[0]).Sort ($oExcel.Range("B2"), 1, $oExcel.Range("C2"), Default, 1, $oExcel.Range("A2"), 1, 0, 1, False, 1, Default, 0, 0, 0)

Give that a go.

-S

[GMK]

Excellent! But now I've run into another problem.

Here's the deal: I have two CSV files that I want to do some cleaning up, then combine into one workbook, then compare them and note the differences on a third sheet. I've gotten to where I can open the CSV files separately, and clean them up, but I'm not quite sure how to move the sheet of the second document after the sheet of the first document (into the first workbook).

Here's what I have so far:

$oExcel.ActiveSheet.Copy (Default, $oExcel.Workbooks("C:\Path\file.xls").Sheets(1))

C:\Path\file.au3 (166) : ==> The requested action with this object has failed.: 
$oExcel.ActiveSheet.Copy (Default, $oExcel.Workbooks("C:\Path\file.xls").Sheets(1)) 
$oExcel.ActiveSheet.Copy (Default, $oExcel.Workbooks("C:\Path\file.xls")^ ERROR

Thanks for all your help so far.

[Locodarwin]

The copy method is not meant to work the way you're using it. It's merely an Excel COM way to get data into Windows clipboard. Therefore, in order to move data between workbooks, you need to open them both as separate objects and then copy between the objects.

Here's an outline to illustrate the basic idea:

1. Open workbook1 as $oExcel1

2. Open workbook2 as $oExcel2

3. Select the worksheet from workbook1 that you want to copy to workbook2

4. Use the copy method on the range of the sheet you want to copy

5. Select the worksheet from workbook2 (create if needed) to copy to

6. Use the paste method on the range of the sheet you want to copy to

Get some code going and try it out; if you have trouble, post your code here and we'll debug it.

Good luck!

-S

[GMK]

I've finally finished the project. I figured out how to copy and paste the data into new sheets, rather than moving one sheet from one workbook to another. Now the only other problem I'm having is that, after having translated a VBA script to AutoIt, I'm finding that it takes a ton longer to do it with AutoIt and I'm not quite sure why. I guess some things are just better with VBA?

Anyway, thanks again for your help. I've been on these boards for less than two weeks, and I already feel like I've learned quite a bit.

Edited May 12, 2007 by GMK

[Locodarwin]

Happy to be of any help.

In general, AutoIt will be a bit slower than Excel VBA. That's an over-simplification, though. Depending on what you need to do, and how you choose to do it, AutoIt can be comparable in speed. Given the fact that AutoIt is much more flexible overall, and can create standalone tools & applications, I think the benefits far outweigh any shortcomings.

Best of luck with your AutoIt and/or Excel endeavors!

-S