GMK Posted May 9, 2007 Posted May 9, 2007 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 Posted May 10, 2007 Posted May 10, 2007 (edited) 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 (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]
GMK Posted May 10, 2007 Author Posted May 10, 2007 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÷ir Þ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 Posted May 10, 2007 Posted May 10, 2007 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]
GMK Posted May 10, 2007 Author Posted May 10, 2007 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 Posted May 11, 2007 Posted May 11, 2007 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]
GMK Posted May 11, 2007 Author Posted May 11, 2007 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 Posted May 11, 2007 Posted May 11, 2007 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]
GMK Posted May 12, 2007 Author Posted May 12, 2007 (edited) 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 Posted May 12, 2007 Posted May 12, 2007 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]
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now