Change the code to this:
====================
Option Compare Database
Const strQName As String = "zExportQuery"
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Sub AMP_1()
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT AMP_ID FROM ADS_Revenue_PRV;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("AMP_NAME", "ADS_Revenue_PRV", _
"AMP_ID = " & rstMgr!AMP_ID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM ADS_Revenue_PRV WHERE " & _
"AMP_ID = " & rstMgr!AMP_ID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, "C:\Elaine\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
dbs.QueryDefs.Delete strTemp
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.Close
Set dbs = Nothing
End Sub
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
I am getting the error '3012'- Object 'zexportQuery' already exists and
it
highlights the line 'Set qdf = dbs.CreateQueryDef(strQName, strSQL)'
This is the entire code:
Option Compare Database
Const strQName As String = "zExportQuery"
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Sub AMP_1()
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field
names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT AMP_ID FROM ADS_Revenue_PRV;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for each
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("AMP_NAME", "ADS_Revenue_PRV", _
"AMP_ID = " & rstMgr!AMP_ID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM ADS_Revenue_PRV WHERE " & _
"AMP_ID = " & rstMgr!AMP_ID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, "C:\Elaine\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub
:
Hi
I am getting the error '3012'- Object 'zexportQuery' already exists
and
it
highlights the line 'Set qdf = dbs.CreateQueryDef(strQName, strSQL)'
:
See the code in this article:
Create a Query and Export multiple "filtered" versions of a Query
(based on
data in another table) to separate EXCEL files via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSepFiles
You can modify the code to change the path for each EXCEL file name
to
include the PlanID value as a folder. If that folder does not
already
exist,
you'll need to create it in the code using MkDir before you do the
export
(TransferSpreadsheet) step.
Try it, and post back if you have questions.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi, I am looking at a sample code to export a report or query for
each
individual variable group and stores it in a specified folder
depending on
the group ID. For the query I need it to export it in Excel and
the
report
in PDF.
Example:
Query: PlanID, PlanName, MemberID,TotalPaid
I want to create an Excel worksheet for each PlanID and save it in
a
separate forlder for each PlanID, for example:
C:\MyReports\"PlanID".xls