Export Reports to Excel based on Salesrep Name in Table 1

[sgupta76]

I have a table called Commission_Main which contains transactions for all Salesreps.

I want to export the transactions for each Salesrep into individual excel spreadsheets using the Salesrep name as the criteria. I am trying to use the docmd.OutputTo but it has no criteria or where clause.

I would like to know how I can export the transactions to different spreadsheets with the Salesrep name as the criteria. I am trying to avoid using VBA to manually open a spreadsheet and fill it in with values from a table.

Any help would be appreciated.
Thanks,
SG

 

[smedvid]

One Approach is to create a query def and then export that query def to an Excel spreadshet.

Basically, you can create a query of distinct Sales people and load it to a record set.

Loop through the record set.
Within the loop
Call a function (MakeQueryDef) that creates a temporary query_def. That query def would basically say Select * from table where salesman='Jones' where 'Jones' is a parameter you pass into the MakeQueryDef function.
Then Export the Temp_query to an Excel file
End Loop.

Here is some old code snippets...
lcSQL = "SELECT CourseID, CourseName, CourseDescr, CourseTotHours, CourseFYTimes, CourseFYHours, ComApproveFlag " & _
"FROM Courses"
lcQueryName = "Temp_Query"

For Each lc_Query In lc_Db.QueryDefs
If Trim(lc_Query.Name) = Trim(lcQueryName) Then
lc_Db.QueryDefs.Delete (lcQueryName)
lc_Db.QueryDefs.Refresh
End If
Next lc_Query

Set lc_Query = lc_Db.CreateQueryDef(lcQueryName, lcSQL)
lc_Db.QueryDefs.Refresh
lc_Db.Close


If Displaymode = "Query" Then
DoCmd.OpenQuery lcQueryName, , acReadOnly
ElseIf Displaymode = "Excel" Then
'Export to Excel
lcErr_Message = "" & vbCr
lcErr_Message = lcErr_Message + "Would You Like to Launch Excel After the Excel File is Created?" & vbCr
Response = MsgBox(lcErr_Message, vbYesNo + vbQuestion + vbDefaultButton2, "Launch Excel&quot
If Response = vbYes Then ' User chose Yes.
' Yes - Start Excel after file created!
ViewExcelFlag = True
Else ' User chose No.
' No Don't Launch Excel
ViewExcelFlag = False
End If
'User must specify file name and storage location.
DoCmd.OutputTo acOutputQuery, lcQueryName, acFormatXLS, , ViewExcelFlag 'lcQueryName

...


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[sgupta76]

Thanks a lot Steve! This is exactly what I was looking for.

Santosh