Sort and Subtotal Excel From VBA

This is a continuation of a recent question. I am now trying to sort and subtotal a spreadsheet from VBA. I have tried to glean from the previous question a method to do this but have been unscuccessful. I have attached an Access database. The procedure ExpLabDist contains the code that works up to a point which I have identified. I have added code from Excel macros to sort and subtotal the spreadsheet created in the code. This code will not work as is. I find the coding in Excel fairly straight forward. However when attempting to duplicate the actions in Access I am confused. So I need assistance to convert the excel macro code to something that will work in Access. Also I would appreciate anyone who could give me some rules of thumb or instruction on the art of converting Excel macros into workable code in Access. Thank you.
TestExcelFormat.mdb
StampITAsked:
Who is Participating?
 
Michael VasilevskyConnect With a Mentor Solutions ArchitectCommented:
For the Sort portion, you're going to have to get the number of rows another way. You should be able to create a function that returns the number of records in your source query and then you can use that to get NumRows. Let me know if you need help doing that.

Otherwise all you needed to do was replace ActiveWorksheet and ActiveCells calls with a with sheet statement. See below.
In general ActiveSheet, ActiveCell, etc. won't work in Access VBA, you need to tell it what sheet to use.
'The sort keys are set first. In this sub function columns D and A.
'Then the entire spreadsheet is sorted based on the keys.

Dim NumRows As Double
NumRows = 59

    With sh
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key _
            :=Range("D2:D" & NumRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        .Sort.SortFields.Add Key _
            :=Range("A2:A" & NumRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortNormal
        With .Sort
            .SetRange Range("A2:J" & NumRows)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    Exc.Visible = True
    Exit Function
'****************End of Sort Macro***************************

Open in new window

0
 
Rory ArchibaldConnect With a Mentor Commented:
(not for points)
You missed qualifying the range arguments for the Key and Setrange:
'The sort keys are set first. In this sub function columns D and A.
'Then the entire spreadsheet is sorted based on the keys.

Dim NumRows As Double
NumRows = 59

    With sh
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key _
            :=.Range("D2:D" & NumRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        .Sort.SortFields.Add Key _
            :=.Range("A2:A" & NumRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortNormal
        With .Sort
            .SetRange sh.Range("A2:J" & NumRows)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    Exc.Visible = True
    Exit Function
'****************End of Sort Macro***************************

Open in new window

0
 
StampITAuthor Commented:
mvasilevsky:
    I added the code as you suggested. However it does not always work. I get "Error #1004. Application-defined or object-defined error". Also I would like assistance on a function to count the rows in the crosstab query that generates the spreadsheet. In addition there is a sub function below the ExpLabDist function in the module that I need to add. The sub code is from Excel. This was in the original question. Thanks.

rorya,
     Are you referring to code mvasilevsky suggested or something else? Either way I do not understand. Please explain further if you would. Thanks.

   
TestExcelFormat.mdb
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Rory ArchibaldCommented:
I was referring to mvasilevsky's code. He/she had left a couple of unqualified references in there.
0
 
StampITAuthor Commented:
rorya:
     I now see the changes you made. I did not before. Could be why I am getting the error. Thanks.
0
 
StampITAuthor Commented:
mvasilevsky:
    I qualified the references as suggested by rorya. The Sort Code now appears to work everytime. thank you. Will take you up on your offer to assist with a function to count the number of rows in the query. Not sure how do it with a crosstab query. Also need assistance with Excel subtotal code. Thanks.
TestExcelFormat.mdb
0
 
Michael VasilevskySolutions ArchitectCommented:
Probably best to close this question an open another one. These are two separate issues and should be addressed to the whole EE community.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.