Jump to content
Wolfiesaxah

Excel Sort Method Attempt

Recommended Posts

Wolfiesaxah

Hi guys, I am using an old version of Excel.au3 which I am not ready to update yet so please help me. The old Excel.au3 does not have any method for SORT. I attempted to create one since i need one but I am getting an error. Here's my sample code:

$oAppl.Activesheet.UsedRange.sort($oAppl.Activesheet.cells(1,5),1,1)

I thought this would work but I get the  

: ==> The requested action with this object has failed.:
$oAppl.Activesheet.UsedRange.sort($oAppl.Activesheet.cells(1,$BidDayColNumber),1,1)
$oAppl.Activesheet.UsedRange.sort($oAppl.Activesheet.cells(1,$BidDayColNumber),1,1)^ ERROR

Any ideas or alternatives on sorting a range without calling from Excel.au3?

Share this post


Link to post
Share on other sites
water

The function that comes with the new Excel UDF lookcs like this. Docu can be found in the online help file.

Func _Excel_RangeSort($oWorkbook, $vWorksheet, $vRange, $vKey1, $iOrder1 = Default, $iSortText = Default, $iHeader = Default, _
        $bMatchcase = Default, $iOrientation = Default, $vKey2 = Default, $iOrder2 = Default, $vKey3 = Default, $iOrder3 = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If Not IsObj($vWorksheet) Then
        If $vWorksheet = Default Then
            $vWorksheet = $oWorkbook.ActiveSheet
        Else
            $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
        EndIf
        If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If $vRange = Default Then
        $vRange = $vWorksheet.Usedrange
    ElseIf Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    $vKey1 = $vWorksheet.Range($vKey1)
    If @error Or Not IsObj($vKey1) Then Return SetError(4, @error, 0)
    If $vKey2 <> Default Then
        $vKey2 = $vWorksheet.Range($vKey2)
        If @error Or Not IsObj($vKey2) Then Return SetError(5, @error, 0)
    EndIf
    If $vKey3 <> Default Then
        $vKey3 = $vWorksheet.Range($vKey3)
        If @error Or Not IsObj($vKey3) Then Return SetError(6, @error, 0)
    EndIf
    If $iHeader = Default Then $iHeader = $xlNo
    If $bMatchcase = Default Then $bMatchcase = False
    If $iOrientation = Default Then $iOrientation = $xlSortColumns
    If $iOrder1 = Default Then $iOrder1 = $xlAscending
    If $iSortText = Default Then $iSortText = $xlSortNormal
    If $iOrder2 = Default Then $iOrder2 = $xlAscending
    If $iOrder3 = Default Then $iOrder3 = $xlAscending
    If Int($oWorkbook.Parent.Version) < 112 Then ; Use Sort method for Excel 2003 and older
        $vRange.Sort($vKey1, $iOrder1, $vKey2, Default, $iOrder2, $vKey3, $iOrder3, $iHeader, Default, $bMatchcase, $iOrientation, Default, $iSortText, $iSortText, $iSortText)
    Else
        ; http://www.autoitscript.com/forum/topic/136672-excel-multiple-column-sort/?hl=%2Bexcel+%2Bsort+%2Bcolumns#entry956163
        ; http://msdn.microsoft.com/en-us/library/ff839572(v=office.14).aspx
        $vWorksheet.Sort.SortFields.Clear
        $vWorksheet.Sort.SortFields.Add($vKey1, $xlSortOnValues, $iOrder1)
        If $vKey2 <> Default Then $vWorksheet.Sort.SortFields.Add($vKey2, $xlSortOnValues, $iOrder2)
        If $vKey3 <> Default Then $vWorksheet.Sort.SortFields.Add($vKey3, $xlSortOnValues, $iOrder3)
        $vWorksheet.Sort.SetRange($vRange)
        $vWorksheet.Sort.Header = $iHeader
        $vWorksheet.Sort.MatchCase = $bMatchcase
        $vWorksheet.Sort.Orientation = $iOrientation
        $vWorksheet.Sort.Apply
    EndIf
    If @error Then Return SetError(7, @error, 0)
    Return $vRange
EndFunc   ;==>_Excel_RangeSort

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

  • Similar Content

    • AnonymousX
      By AnonymousX
      Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
      I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
      I saw this code:
      With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
      Is there another include file I need?
      I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
      $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
    • YouriKamperman
      By YouriKamperman
      I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
      This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
      How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
      This is what my script looks like:
      Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
      Local $cDate = StringReplace($Yesterday, "/", "-")
      Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
      Local $oExcel = _Excel_Open()
      Local $oWorkbook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
    • Skysnake
      By Skysnake
      I am tracking this topic by @LarsJ.  It is very advanced and overkill for what I am currently trying to do.
       
      Problem is this.
      Listview contains columns, one of which is right aligned and gets populated by float values, such as 123.99.  Some do not have decimals ie 124.00 and on sort gets truncated to 124.  Its obviously still the same value, but the display has reset.
      ; line below is for list VIEW ;..................................0.........1......2............ $cListView = GUICtrlCreateListView("CUSTOMER|AMOUNT|DESCRIPTION", 8, 152, 764, 279) GUICtrlSetBkColor($cListView, $GUI_BKCOLOR_LV_ALTERNATE) ; alternate between the listview background color and the listview item background color GUICtrlSetBkColor($cListView, $LVStdClr) ; Set the background color for the listview _GUICtrlListView_SetColumnWidth($cListView, 0, 120) ; -- the client name _GUICtrlListView_SetColumnWidth($cListView, 1, 90) ;-- the amount _GUICtrlListView_JustifyColumn($cListView, 1, 1) ; 1 - Text is right aligned _GUICtrlListView_SetColumnWidth($cListView, 2, 200) ; the description What I am looking for is something native and simple like a 
          _GUICtrlListView_SetColumnFormat($cListView, 1, "%.2f") ;  1 - column is stringformatted to "%.2f"
      So that after each sort it will appear as it was in the original rendering.
      Is there something like this? I have not been able to find a simple solution.

      Thanks.
      Skysnake
    • Eminence
      By Eminence
      Hello,
      Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
      Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 
       
      Any help is appreciated. Thanks in advance.
    • JNutt
      By JNutt
      I am trying to close an excel file that was not opened with _Excel_Open.  How do I found the excel application object?  I'm new and I am used to files and folders names, so an 'object' is new to me.  I have the info too and simply spy, but I don't know which info is the object name/string.  In the example from help doc's I see the code below and I tried justin pasting it into Scite.
      <
      Local $oExcel1 = ObjCreate("Excel.Application")
      ; Close the Excel instance which was not opened by _Excel_Open
      ; (will still be running because it was not opened by _Excel_Open)
      _Excel_Close($oExcel1)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Sleep(2000)
      Local $aProcesses = ProcessList("Excel.exe")
      MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")
      >
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.