Export by group to a specific folder and name

[Elaine]

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

 

[Ken Snell MVP]

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.

 

[Elaine]

Hi

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

 

[Elaine]

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

 

[Ken Snell MVP]

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)'

 

[Elaine]

I still receive the same error...

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

 

[Ken Snell MVP]

Probably what's happened is that you created that query and then your code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same as I
provided. Try it then.

--

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

I still receive the same error...

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

 

[Elaine]

Now I get the error 3265- Item not found in this collection, and highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"

Probably what's happened is that you created that query and then your code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same as I
provided. Try it then.

--

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

I still receive the same error...

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

 

[Ken Snell MVP]

Create a new query as a "dummy". Put any table in the query and select one
field from it. Save the query and name it qry_z_DUMMY. The code that you're
using assumes that there is at least one query already in the database, and
it sounds as if your database file doesn't have any queries yet.
--

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

Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"

Probably what's happened is that you created that query and then your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same as
I
provided. Try it then.

--

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

I still receive the same error...


:

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

 

[Elaine]

Hi Ken

I did it but still gives me the same error...

Create a new query as a "dummy". Put any table in the query and select one
field from it. Save the query and name it qry_z_DUMMY. The code that you're
using assumes that there is at least one query already in the database, and
it sounds as if your database file doesn't have any queries yet.
--

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

Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"

Probably what's happened is that you created that query and then your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Ken Snell MVP]

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over the
strTemp text in the line. What value do you see in the popup window?

--

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

Hi Ken

I did it but still gives me the same error...

Create a new query as a "dummy". Put any table in the query and select
one
field from it. Save the query and name it qry_z_DUMMY. The code that
you're
using assumes that there is at least one query already in the database,
and
it sounds as if your database file doesn't have any queries yet.
--

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

Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Elaine]

What it does is that it keeps with the error several times and after a couple
of times clicking on the Run button, it takes the value of the virst group
name and exports the report. I founf the report in the location, but it does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over the
strTemp text in the line. What value do you see in the popup window?

--

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

Hi Ken

I did it but still gives me the same error...

Create a new query as a "dummy". Put any table in the query and select
one
field from it. Save the query and name it qry_z_DUMMY. The code that
you're
using assumes that there is at least one query already in the database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Ken Snell MVP]

In what type of object are you trying to run this code? And in what event
procedure? I just noticed that your original code does not show a specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL. This
code is designed to export filtered versions of a single query.
--

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

What it does is that it keeps with the error several times and after a
couple
of times clicking on the Run button, it takes the value of the virst group
name and exports the report. I founf the report in the location, but it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over
the
strTemp text in the line. What value do you see in the popup window?

--

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

Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and select
one
field from it. Save the query and name it qry_z_DUMMY. The code that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Elaine]

I am trying to run the code in a table. I saved the code as a module and ran
it. I would like to export a table or query to Excel and create a file for
each group. I also would like to be able to filter a report by group and
export it to a specific location as PDF for each group separately.

In what type of object are you trying to run this code? And in what event
procedure? I just noticed that your original code does not show a specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL. This
code is designed to export filtered versions of a single query.
--

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

What it does is that it keeps with the error several times and after a
couple
of times clicking on the Run button, it takes the value of the virst group
name and exports the report. I founf the report in the location, but it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over
the
strTemp text in the line. What value do you see in the popup window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and select
one
field from it. Save the query and name it qry_z_DUMMY. The code that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Ken Snell MVP]

You cannot run code from a table in ACCESS. Are you using a form that shows
a table's data? Is the code that you posted the entire code in that module
that you created? If yes, it will not work at all because the code is not
part of a subroutine or function in that module.

What you should do is create a form that contains a command button. Create
an event procedure for the button's Click event, and paste all the code from
your regular module (except for the Option Compare Database line) between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click event.

If you do this, then you would open the form, click the command button, and
the code will run.
--

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

I am trying to run the code in a table. I saved the code as a module and
ran
it. I would like to export a table or query to Excel and create a file
for
each group. I also would like to be able to filter a report by group and
export it to a specific location as PDF for each group separately.

In what type of object are you trying to run this code? And in what event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL. This
code is designed to export filtered versions of a single query.
--

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

What it does is that it keeps with the error several times and after a
couple
of times clicking on the Run button, it takes the value of the virst
group
name and exports the report. I founf the report in the location, but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.





:

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over
the
strTemp text in the line. What value do you see in the popup window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and
select
one
field from it. Save the query and name it qry_z_DUMMY. The code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Elaine]

Hi Ken

I am still having problems to get this to work. I created a form with a
command button. On the "Build Event" section I pasted the following:

Const strQName As String = "zExportQuery"

Private Sub Command0_Click()



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






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

When I clicked the command button it gave me the error 3265- Item not found
in this collection, and in the debug highlighted the line "set
qdf=dbs.Querydefs(strTemp)".
It did create an Excel file for the first group.

You cannot run code from a table in ACCESS. Are you using a form that shows
a table's data? Is the code that you posted the entire code in that module
that you created? If yes, it will not work at all because the code is not
part of a subroutine or function in that module.

What you should do is create a form that contains a command button. Create
an event procedure for the button's Click event, and paste all the code from
your regular module (except for the Option Compare Database line) between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click event.

If you do this, then you would open the form, click the command button, and
the code will run.
--

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

I am trying to run the code in a table. I saved the code as a module and
ran
it. I would like to export a table or query to Excel and create a file
for
each group. I also would like to be able to filter a report by group and
export it to a specific location as PDF for each group separately.

In what type of object are you trying to run this code? And in what event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL. This
code is designed to export filtered versions of a single query.
--

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



What it does is that it keeps with the error several times and after a
couple
of times clicking on the Run button, it takes the value of the virst
group
name and exports the report. I founf the report in the location, but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.





:

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr over
the
strTemp text in the line. What value do you see in the popup window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and
select
one
field from it. Save the query and name it qry_z_DUMMY. The code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection, and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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)

 

[Ken Snell MVP]

Create a form.

Put a command button on the form. Name it "cmdExport". Click on the command
button (in design view).

Open the Properties window. Click on the Event tab. Click in the box next to
the On Click property. Click the three-dot box at far right side of box. If
asked, select Event Procedure from the list.

The Visual Basic Editor will open. On the screen, you see this code:

Private Sub cmdExport_Click()

End Sub


In that blank line between "Private Sub" line and "End Sub" line, paste all
the code that you're wanting to use.

Close the Visual Baisic Editor Window.

Save the form, and close it.

Open the form in Form view, and click the button.

--

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

Hi Ken

I am still having problems to get this to work. I created a form with a
command button. On the "Build Event" section I pasted the following:

Const strQName As String = "zExportQuery"

Private Sub Command0_Click()



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






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

When I clicked the command button it gave me the error 3265- Item not
found
in this collection, and in the debug highlighted the line "set
qdf=dbs.Querydefs(strTemp)".
It did create an Excel file for the first group.

You cannot run code from a table in ACCESS. Are you using a form that
shows
a table's data? Is the code that you posted the entire code in that
module
that you created? If yes, it will not work at all because the code is not
part of a subroutine or function in that module.

What you should do is create a form that contains a command button.
Create
an event procedure for the button's Click event, and paste all the code
from
your regular module (except for the Option Compare Database line) between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click event.

If you do this, then you would open the form, click the command button,
and
the code will run.
--

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

I am trying to run the code in a table. I saved the code as a module
and
ran
it. I would like to export a table or query to Excel and create a file
for
each group. I also would like to be able to filter a report by group
and
export it to a specific location as PDF for each group separately.


:

In what type of object are you trying to run this code? And in what
event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL.
This
code is designed to export filtered versions of a single query.
--

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



What it does is that it keeps with the error several times and after
a
couple
of times clicking on the Run button, it takes the value of the virst
group
name and exports the report. I founf the report in the location,
but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.





:

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr
over
the
strTemp text in the line. What value do you see in the popup
window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and
select
one
field from it. Save the query and name it qry_z_DUMMY. The code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection,
and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and
then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code
the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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/




message
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)

 

[Elaine]

It created the Excel file for the first group but not for the other groups.
It has the error again at the line of Set qdf = dbs.QueryDefs(strTemp)

Private Sub cmdExport_Click()

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

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

Create a form.

Put a command button on the form. Name it "cmdExport". Click on the command
button (in design view).

Open the Properties window. Click on the Event tab. Click in the box next to
the On Click property. Click the three-dot box at far right side of box. If
asked, select Event Procedure from the list.

The Visual Basic Editor will open. On the screen, you see this code:

Private Sub cmdExport_Click()

End Sub


In that blank line between "Private Sub" line and "End Sub" line, paste all
the code that you're wanting to use.

Close the Visual Baisic Editor Window.

Save the form, and close it.

Open the form in Form view, and click the button.

--

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

Hi Ken

I am still having problems to get this to work. I created a form with a
command button. On the "Build Event" section I pasted the following:

Const strQName As String = "zExportQuery"

Private Sub Command0_Click()



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






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

When I clicked the command button it gave me the error 3265- Item not
found
in this collection, and in the debug highlighted the line "set
qdf=dbs.Querydefs(strTemp)".
It did create an Excel file for the first group.

You cannot run code from a table in ACCESS. Are you using a form that
shows
a table's data? Is the code that you posted the entire code in that
module
that you created? If yes, it will not work at all because the code is not
part of a subroutine or function in that module.

What you should do is create a form that contains a command button.
Create
an event procedure for the button's Click event, and paste all the code
from
your regular module (except for the Option Compare Database line) between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click event.

If you do this, then you would open the form, click the command button,
and
the code will run.
--

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



I am trying to run the code in a table. I saved the code as a module
and
ran
it. I would like to export a table or query to Excel and create a file
for
each group. I also would like to be able to filter a report by group
and
export it to a specific location as PDF for each group separately.


:

In what type of object are you trying to run this code? And in what
event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL.
This
code is designed to export filtered versions of a single query.
--

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



What it does is that it keeps with the error several times and after
a
couple
of times clicking on the Run button, it takes the value of the virst
group
name and exports the report. I founf the report in the location,
but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.





:

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the curosr
over
the
strTemp text in the line. What value do you see in the popup
window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query and
select
one
field from it. Save the query and name it qry_z_DUMMY. The code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries yet.
--

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


Now I get the error 3265- Item not found in this collection,
and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query and
then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the code
the
same
as
I
provided. Try it then.

--

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


I still receive the same error...


:

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

 

[Ken Snell MVP]

OK, now that it's partially working, in this line of code:

' 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


Delete the line
dbs.QueryDefs.Delete strTemp


Then try it again.

--

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

It created the Excel file for the first group but not for the other
groups.
It has the error again at the line of Set qdf = dbs.QueryDefs(strTemp)

Private Sub cmdExport_Click()

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

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

Create a form.

Put a command button on the form. Name it "cmdExport". Click on the
command
button (in design view).

Open the Properties window. Click on the Event tab. Click in the box next
to
the On Click property. Click the three-dot box at far right side of box.
If
asked, select Event Procedure from the list.

The Visual Basic Editor will open. On the screen, you see this code:

Private Sub cmdExport_Click()

End Sub


In that blank line between "Private Sub" line and "End Sub" line, paste
all
the code that you're wanting to use.

Close the Visual Baisic Editor Window.

Save the form, and close it.

Open the form in Form view, and click the button.

--

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

Hi Ken

I am still having problems to get this to work. I created a form with
a
command button. On the "Build Event" section I pasted the following:

Const strQName As String = "zExportQuery"

Private Sub Command0_Click()



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






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

When I clicked the command button it gave me the error 3265- Item not
found
in this collection, and in the debug highlighted the line "set
qdf=dbs.Querydefs(strTemp)".
It did create an Excel file for the first group.


:

You cannot run code from a table in ACCESS. Are you using a form that
shows
a table's data? Is the code that you posted the entire code in that
module
that you created? If yes, it will not work at all because the code is
not
part of a subroutine or function in that module.

What you should do is create a form that contains a command button.
Create
an event procedure for the button's Click event, and paste all the
code
from
your regular module (except for the Option Compare Database line)
between
the Private Sub and End Sub lines that you'll find in the Visual Basic
Editor after you create the event procedure for the button's Click
event.

If you do this, then you would open the form, click the command
button,
and
the code will run.
--

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



I am trying to run the code in a table. I saved the code as a module
and
ran
it. I would like to export a table or query to Excel and create a
file
for
each group. I also would like to be able to filter a report by
group
and
export it to a specific location as PDF for each group separately.


:

In what type of object are you trying to run this code? And in what
event
procedure? I just noticed that your original code does not show a
specific
function or subroutine as the procedure.

This code is not designed for exporting a report directly to EXCEL.
This
code is designed to export filtered versions of a single query.
--

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



What it does is that it keeps with the error several times and
after
a
couple
of times clicking on the Run button, it takes the value of the
virst
group
name and exports the report. I founf the report in the location,
but
it
does
not do it when running the first time.
Taking out the breakpoint, does not generate the report.





:

Put a breakpoint on this line of code:

Set qdf = dbs.QueryDefs(strTemp)


Run the code. When the code breaks on that line, hover the
curosr
over
the
strTemp text in the line. What value do you see in the popup
window?

--

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


Hi Ken

I did it but still gives me the same error...

:

Create a new query as a "dummy". Put any table in the query
and
select
one
field from it. Save the query and name it qry_z_DUMMY. The
code
that
you're
using assumes that there is at least one query already in the
database,
and
it sounds as if your database file doesn't have any queries
yet.
--

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


Now I get the error 3265- Item not found in this
collection,
and
highlights
the line
Set qdf = dbs.QueryDefs(strTemp)"


:

Probably what's happened is that you created that query
and
then
your
code
didn't finish so it wasn't deleted.

Go to your query window and delete that query. Keep the
code
the
same
as
I
provided. Try it then.

--

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


message
I still receive the same error...


:

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

 

[marc kaslack]

k