Subtotal with tax

[Renee]

I have a query that I export from access to excel and would like to subtotal
each invoice listed on the sheet but also include the tax and grand total of
the invoice. Is this possible?

 

[Mike]

Yes it can
I can help if you want to send me a copy of the workbook.

(e-mail address removed)

 

[Renee]

You can't just explain it to me, cause I am going to be exporting and doing
these totals on a weekly basis so I need to know how.

 

[Mike]

It will take vba code to do this. Do you know how to write code ? If it was
me instead of exporting it from access I would have excel pull the data I
needed.

 

[Mike]

I would set it up so that all you do is press a button.

 

[Renee]

I can pull the data from Access that I need? Don't think I have heard of
that. I have entered some vba code but not created my own, I am just starting
to learn how to write it.

 

[Mike]

With a few changes you could use this. If you would send my the workbook I
could get you started. Its up to you.

Private Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim rowNumber As Long

'C:\PathToYourMdb\Ilsa.mdb (Change)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\PathToYourMdb\Ilsa.mdb;Persist Security Info=False"

'sSQL = "Replace with your query"
sSQL = "SELECT Field1, Field2 From TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
rowNumber = 2 'Starting Row Number for data
Do While (Not rs.EOF)
Range("A" & rowNumber) = rs.Fields("Field1").Value
Range("B" & rowNumber) = rs.Fields("Field2").Value
rowNumber = rowNumber + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[Renee]

Wow, that sounds like it would take some time for me to figure out how to do.
I will have to look into it for later. For now can you help me with the code
for when the query is exported to excel?

 

[Mike]

I can but trust me it would be easier for me if you send me a copy of the
workbook.

 

[Renee]

But then how do I do it for the rest of the time? As I would need to do this
once a week.

 

[Mike]

I could set it up to pull in the data from the workbook that was exported
from access
Press the button and it will run.

 

[Mike]

Send to (e-mail address removed)

How do I go about sending you a copy of the workbook?

 

[Renee]

There, email is sent from (e-mail address removed)

Send to (e-mail address removed)