Convert Excel subtotal code to work in Access VBA

In the module in the attached database there is a sub function called macSubtotalLabDistrDet that was copied from excel. I need to incorporate this code into the Function above ExpLabDist. I need assistance in converting this code so that it will work from Access. Will the number of columns need to be determined from the crosstab query that generates the spreadsheet?
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.

Rey Obrero (Capricorn1)Commented:
you can use this to get the number of rows

numRows=sh.usedrange.rows.count
0
Rey Obrero (Capricorn1)Commented:
you can use this to get the number of columns

numCols=sh.usedrange.columns.count
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
StampITAuthor Commented:
numCols=sh.usedrange.coulumns.count resolves the count columns issue. Thank you. I still need assistance in converting the macSubtotalLabDistrDet code from excel into a form that will work from Access VBA. Thanks.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Rey Obrero (Capricorn1)Commented:
post a sample excel file with the result you want
0
StampITAuthor Commented:
I have managed through much trial and error to create the spreadsheet as I need it. Good learning experience I think. Only one problem remains. I am trying to write "page x of x" in the spreadsheet footer. The code is under the heading "Final Format" in function ExpLabDist. Can this be done? I have attached the database and the excel spreadsheet as I would like it to be. Thanks for the help.
TestExcelFormat.mdb
LabDistrDet122611.xlsx
0
Rory ArchibaldCommented:
It's basically just:
.CenterFooter = "Page &P of &N"

Open in new window

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.