Jump to content
Sign in to follow this  
GMK

Excel question

Recommended Posts

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.)

Share this post


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

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

(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]

Share this post


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

Share this post


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


(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]

Share this post


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

Share this post


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


(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]

Share this post


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

Share this post


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


(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]

Share this post


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

Share this post


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


(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]

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  

×