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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael VasilevskySolutions 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
(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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.