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?
 
Rey Obrero (Capricorn1)Commented:
you can use this to get the number of columns

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

numRows=sh.usedrange.rows.count
0
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
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.