VBA to insert Totals row in table

[Jonathan_11]

Hi All,

My problem is I am creating a invoice which is being exported from access to excel, which basically needs all the items and their price and then the overall total.

I just want to insert a row which has the total for the price column. Alternatively I could do a SUM calculation in excel, again I am having no luck doing this from Access.

Any help would be extremely appreciated!

Thanks,

Jonathan Smith

 

[alansidman]

Jonathan;
A few more details on how you are attempting to do this would be helpful, but since your thread title refers to tables, I am making a guess. If you are attempting to do a calculation in the table, then this is an issue. Calculations are performed in Queries and Forms and should normally not be stored. You can output your calculations from a form or a report directly to Excel.

If you are exporting to excel, why not do the calculation in Excel. Further, why not create your invoice in Access through a report? Why does it have to go to excel?

Alan

 

[Jonathan_11]

Hi Alan,

Firstly thank-you very much for your reply.

The invoice needs to be outputed as it's own Excel document as it needs to be automatically attached to a email and then sent to the customer.

Ideally I would just be able to do a SUM calculation in the Excel document, excuse me if I am wrong but I am having big problems when attempting to do this simple calculation. I can format the document but I am struggling big time for the calculation.

Thank-you once again for your help, I hope it is clearer now what I am trying to do.

Jonathan

 

[Trevor G]

Welcome to the Forum Jonathan,

What method are you using for the Export and which version of MS Access are you using?

 

[Jonathan_11]

Thanks Trevor,

I am creating a query in SQL, then placing this into a table. I am then exporting this information to Excel. I am working with the 2007 version.

 

[Trevor G]

Are you using a macro to export or vba or something else?

 

[Jonathan_11]

Yes it is a macro, I think I have solved it now......


i = 11
w = 0

Do
Set objRange10 = objSht.Cells(i, 4)
w = objRange10 + w
i = i + 1
Loop Until objSht.Cells(i, 4) = ""
objRange5 = w

I just couldnt get the VBA to do the simple calculation, Thank-you very much for your time, I am new to VBA you see!!

 

[Trevor G]

Pleased to read you have a working solution.