Re: Export by group to a specific folder and name

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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/




"Elaine" <Elaine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3BDF4573-6E70-4793-8781-62228AF3606E@xxxxxxxxxxxxxxxx
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


"Elaine" wrote:

Hi

I am getting the error '3012'- Object 'zexportQuery' already exists and
it
highlights the line 'Set qdf = dbs.CreateQueryDef(strQName, strSQL)'



"Ken Snell MVP" wrote:

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 TransferSpread***
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
(TransferSpread***) step.

Try it, and post back if you have questions.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




"Elaine" <Elaine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5331DF98-B267-4DFE-987C-71076F1AE209@xxxxxxxxxxxxxxxx
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 work*** for each PlanID and save it in a
separate forlder for each PlanID, for example:
C:\MyReports\"PlanID".xls









.

Quantcast