exporting to a pre-formulated excel sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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
 
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).
 
Yep,
I used the UNION ALL, worked like a charm. This is a great site to exchange
ideas!
Thanks
Todd
 
Back
Top