none
Is It Possible To Launch An Office Application's Wizard Through A Script, For Example Launch The Excel Sort Wizard? RRS feed

  • Question

  • I am creating an Excel application, based on VBS code I found on the Hey Scripting Guy site,to read and modify an Excel spreadsheet.  This Excel application creates a custom toolbar via VBA code so the user is able to select one of my toolbar menu items to launch a few VBA routines to retrieve some Active Directory properties for display and editing.  The spreadsheets for User, Group, and Computer each have three header rows for (1) the AD Schema property name, (2) the Active Directory Users and Computers (ADUC) field display name, and (3) a Local Use field name.  For example, the three column headings for a field on the computer sheet has  the schema property name "SN", the ADUC field name as "Last Name", and local use as the "Serial Number".  A typical spreadsheet, with only one header row, can be sorted simply by clicking on the select all cell in the corner above row 1 and column A and then select the option to sort; but, this practice will destroy the functionality of my spreadsheet by rearranging the three header rows.  I would like the user to be able to sort the spreadsheet in a safe way by providing a menu item on my custom toolbar, which could possibly run a VBA routine to select all but the first two rows of the spreadsheet containing data so row three will act as the header row, but the tricky part for me is to then have the VBA code make the Excel Sort Wizard appear automatically for the user.  I know that perhaps using Send Keys might work, but this might make my application work on only a particular version of Excel, so I would rather avoid this approach if at all possible.  Despite my best efforts, I have not found a way to programmatically launch the Excel Sort Wizard, the closest code resembling what I want to do is "Range("A1").FunctionWizard", but there is no ".SortWizard" method for the Range object.  After an appropriate range has been programmatically selected, do you know of a way to programmatically launch the Excel sort wizard?

    Thursday, October 20, 2011 9:16 AM

Answers

  • Sub dural()
    Application.Dialogs(xlDialogSort).Show
    End Sub

    gsnu201109
    Thursday, October 20, 2011 10:36 AM
    Moderator
  • Gary's Student, I just could not let this problem go based on your last comment, so I tried a few more things.  What I did was start the Excel macro recorder and looked at the code that was produced.  Interestingly enough you get different results if you sort with or without an auto filter applied, so I had to deactivate my auto filter on the header row in order to capture the code for configuring the Sort Wizard, so after a little bit of rearranging to beautify the code that the Excel macro recorder gave me it looks like:


    Sub subSort()
        With ActiveWorkbook.Worksheets("User").Sort
            .SortFields.CLEAR
            .SortFields.Add _
                Key:=Range("A4:A278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add _
                Key:=Range("B4:B278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add _
                Key:=Range("C4:C278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SetRange Range("A3:BH278")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            '.Apply
        End With
        Application.Dialogs(xlDialogSort).Show
    End Sub


    This code did populate the Sort Wizard’s sort fields and options.  I am not sure that all of this code is required to populate the Sort Wizard with what is needed, because some code could likely be omitted with proper testing as the macro recorder often explicitly defines many of the default settings that need not be included to achieve the same result.  Within the subSort subroutine, I like that I did not have to select a particular cell to make the sort work, so the user's current cell selection remains, but I don't like that the sort ranges are hard coded and explicitly defined within this example; because if these ranges must be supplied as a required parameter, they would need to be determined dynamically to work properly for all cases, as rows are frequently added or subtracted.  It would appear that the code I supplied in my previous post, which I copied from another forum post found on the internet, was probably for passing parameters to the sort wizard from an earlier version of Excel, probably for the Sort Wizard that only allowed three criteria to be specified.  For the version I am using now, Excel 2007, the macro recorder captured the needed code to create the subSort subroutine shown above.


    Update to this post:  After customizing the code that I got from the Excel Macro recorder to create the subSort shown above, I determined that what I really want is the simplified code shown below.  At some point I may want the previous Sort Wizard criteria cleared, but for now I remarked out this line of code, so the user's previous Sort Wizard criteria will be remembered the next time they launch the wizard.


    Sub subSort()
        With ActiveSheet.Sort
            '.SortFields.Clear
            .SetRange Range("A3")
            .Header = xlYes
            .Orientation = xlTopToBottom
        End With
        Application.Dialogs(xlDialogSort).Show
    End Sub


    This simplified and improved version has several benefits over what was shown previously, as it does not involve selecting a cell which would alter the user's current cell selection, but in specifying just the first cell of the sort range, the Sort Wizard automatically determines the appropriate overall range bound by the first empty row at the bottom and the first empty column at the right, which is the behavior that I want without me having to write the code to determine these dynamically and set these parameters for the sort.

    • Edited by Michael Weigert Friday, October 21, 2011 12:47 AM Added the "Update to this post" to show how I discovered the best answer to my problem.
    • Marked as answer by Michael Weigert Friday, October 21, 2011 12:48 AM
    Thursday, October 20, 2011 1:47 PM

All replies

  • Sub dural()
    Application.Dialogs(xlDialogSort).Show
    End Sub

    gsnu201109
    Thursday, October 20, 2011 10:36 AM
    Moderator
  • Gary's Student code, "Application.Dialogs(xlDialogSort).Show", gave me what I am looking for once I figured out how to use it properly.  I planned on writing code to select the proper range of cells containing data to be sorted, but as I selected the range and used the code to launch the sort wizard, I could not get the "My data has headers" checked by default, nor does the code, "Application.Dialogs(xlDialogSort).Show arg8:=xlYes" help in this situation even though the arg8 parameter is supposed to indicate the presence of a header row.  But then I stumbled into trying to select just one cell, A3, the first cell in the range intended to be the header row and found that just, "Application.Dialogs(xlDialogSort).Show" gives me the header row checked and the range selected for sorting is automatically determined as the range with data down to the first blank row and right to the first blank column, so now I don't have to write the code to select this range (pretty groovy feature, thanks Microsoft!).  So the following code gives me what I was looking for:

        Range("A3").Select
        Application.Dialogs(xlDialogSort).Show 


    Thursday, October 20, 2011 10:58 AM
  • This is an excellent question!  Most users would like a Dialog or Form presented to them to be "pre-loaded" with default values in the various fields.  If you do not receive an answer in a reasonable amount of time, consider opening a new post here:

    http://answers.microsoft.com/en-us/office/forum/customize?page=1&tab=all

    or here:

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads


    gsnu201109
    Thursday, October 20, 2011 11:41 AM
    Moderator
  • Gary's Student, in reference to your last reply, during my search for an answer I noticed something similar to this code posted to another forum which passes more parameters:

    Sub MySort()
        Range("A3").Select
        Application.Dialogs(xlDialogSort).Show _
           arg1:=xlSortRows, _
           arg2:="Container", arg3:=xlAscending, _
           arg4:="Name", arg5:=xlAscending, _
           arg8:=xlYes
    End Sub

    However, when I tried this code, the specified parameters were ignored, so I don't think this is quite how it is to be done, something appears to missing.  At this point I am satisfied that the user gets the "My Data Has Headers" option checked by default and they can select the fields that they may which to sort by, but I would be interested in knowing how to populate the sort criteria into the sort wizard as I might need code to do that some day...

     

    Thursday, October 20, 2011 12:34 PM
  • If I find a solution, I will update this post.
    gsnu201109
    Thursday, October 20, 2011 1:30 PM
    Moderator
  • Gary's Student, I just could not let this problem go based on your last comment, so I tried a few more things.  What I did was start the Excel macro recorder and looked at the code that was produced.  Interestingly enough you get different results if you sort with or without an auto filter applied, so I had to deactivate my auto filter on the header row in order to capture the code for configuring the Sort Wizard, so after a little bit of rearranging to beautify the code that the Excel macro recorder gave me it looks like:


    Sub subSort()
        With ActiveWorkbook.Worksheets("User").Sort
            .SortFields.CLEAR
            .SortFields.Add _
                Key:=Range("A4:A278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add _
                Key:=Range("B4:B278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SortFields.Add _
                Key:=Range("C4:C278"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            .SetRange Range("A3:BH278")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            '.Apply
        End With
        Application.Dialogs(xlDialogSort).Show
    End Sub


    This code did populate the Sort Wizard’s sort fields and options.  I am not sure that all of this code is required to populate the Sort Wizard with what is needed, because some code could likely be omitted with proper testing as the macro recorder often explicitly defines many of the default settings that need not be included to achieve the same result.  Within the subSort subroutine, I like that I did not have to select a particular cell to make the sort work, so the user's current cell selection remains, but I don't like that the sort ranges are hard coded and explicitly defined within this example; because if these ranges must be supplied as a required parameter, they would need to be determined dynamically to work properly for all cases, as rows are frequently added or subtracted.  It would appear that the code I supplied in my previous post, which I copied from another forum post found on the internet, was probably for passing parameters to the sort wizard from an earlier version of Excel, probably for the Sort Wizard that only allowed three criteria to be specified.  For the version I am using now, Excel 2007, the macro recorder captured the needed code to create the subSort subroutine shown above.


    Update to this post:  After customizing the code that I got from the Excel Macro recorder to create the subSort shown above, I determined that what I really want is the simplified code shown below.  At some point I may want the previous Sort Wizard criteria cleared, but for now I remarked out this line of code, so the user's previous Sort Wizard criteria will be remembered the next time they launch the wizard.


    Sub subSort()
        With ActiveSheet.Sort
            '.SortFields.Clear
            .SetRange Range("A3")
            .Header = xlYes
            .Orientation = xlTopToBottom
        End With
        Application.Dialogs(xlDialogSort).Show
    End Sub


    This simplified and improved version has several benefits over what was shown previously, as it does not involve selecting a cell which would alter the user's current cell selection, but in specifying just the first cell of the sort range, the Sort Wizard automatically determines the appropriate overall range bound by the first empty row at the bottom and the first empty column at the right, which is the behavior that I want without me having to write the code to determine these dynamically and set these parameters for the sort.

    • Edited by Michael Weigert Friday, October 21, 2011 12:47 AM Added the "Update to this post" to show how I discovered the best answer to my problem.
    • Marked as answer by Michael Weigert Friday, October 21, 2011 12:48 AM
    Thursday, October 20, 2011 1:47 PM
  • It is my turn to thank you.  Nice analysis.
    gsnu201109
    Thursday, October 20, 2011 2:51 PM
    Moderator
  • hi,
     
    With ActiveSheet
        .Rows("4:" & .Cells.Find("*", .Cells.Item(1), , , , xlPrevious).Row).Select
    End With
    Application.Dialogs(xlDialogSort).Show
     --
    isabelle
     
     
    Thursday, October 20, 2011 3:43 PM
  • for that the box "My data has headers" is checked when loading "Dialogs(xlDialogSort)"
     
    'xlDialogSort orientation, key1, order1, key2, order2, key3, order3, header, custom, case
     With ActiveSheet
    b = .Cells.Item(1).Address
        x = .Cells.Find("*", .Cells.Item(1), , , , xlPrevious).Row
        .Rows("3:" & x).Select
    End With
    Application.Dialogs(xlDialogSort).Show , , , , , , , 1
     --
    isabelle
     
     
    Thursday, October 20, 2011 3:59 PM