exporting to a pre-formulated excel sheet

[tamxwell]

I need to export the results of a query to Excel (on desktop) The
spreadsheets needs to have some formulas already in place. I am doing Sums on
fields that need to be well, sumed. here is my VB statement.
Thanks Todd



Private Sub Command1_Click()
On Error GoTo Err_Command1_Click


Dim strPath As String

strPath = GetDesktopFolder & "\" & InputBox("What do you want to name the
file?")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryAdjBadDebt", _
strPath, 1

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click


End Sub

Function GetDesktopFolder() As String
Dim obj As Object
Set obj = CreateObject("WScript.Shell")
GetDesktopFolder = obj.SpecialFolders("Desktop")

End Function

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim strPath As String

strPath = GetDesktopFolder & "\" & InputBox("What do you want to name the
file?")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryAdjBadDebtCustomer", _
strPath, 1

Exit_Command2_Click:

Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Private Sub cmd6_Click()
On Error GoTo Err_cmd6_Click

Dim stDocName As String

stDocName = "qryAdjBadDebt"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmd6_Click:
Exit Sub

Err_cmd6_Click:
MsgBox Err.Description
Resume Exit_cmd6_Click

End Sub
Private Sub cmd7_Click()
On Error GoTo Err_cmd7_Click

Dim stDocName As String

stDocName = "qryAdjBadDebtCustomer"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmd7_Click:
Exit Sub

Err_cmd7_Click:
MsgBox Err.Description
Resume Exit_cmd7_Click

End Sub


Private Sub Command28_Click()


On Error GoTo Err_Command28_Click

Dim stDocName As String

stDocName = "qryBadDebtCustomerSub"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

 

[Ken Snell [MVP]]

And the question or problem is.....?

 

[tamxwell]

Ken,

Thanks for the response. I built a number of queries, I need them to sum 5
columns that need the amounts (Dollars) at the bottom roll of the query. The
query is say Branch, Customer number. Which would f course return just their
info. I have tried everything to get Access to do this. I figured, and maybe
incorrectly, that i might be able to just export the Query info to the Excel
spredsheet and do the calculations there. Have the 5 columns pre-formulated
and it dump the result inand push the formula down. It will do this if say I
run the query and there is 8 rolls of data, then re-run a different company
with less rolls of data. The formula moves up and does the sums. It will not
move the formula down and do the sums. I am still working on re-writing the
queries, but nothing has seemed to work. This database is linked to an excel
spread sheet that pulls it's a text file from the main frame, I'm just
looking at any and everything.
Todd

 

[John Nurick]

Hi Todd,

If you just need the totals for each company, the standard way in Access
is to use a "totals query" (look for "Total records in a query (MDB)" in
Help).

If you want to print out the detail records followed by a total, the
standard way is to use a report (see "Calculate a total or other
aggregate values" in Help).

 

[tamxwell]

Yep,
I used the UNION ALL, worked like a charm. This is a great site to exchange
ideas!
Thanks
Todd