Export Data in Access Table to Mulitple Excel Files

[Guest]

I have a table of employees and their managers. I need to send an Excel file
to each manager to review the list of their employees. Is there a way to
export the employee data to separate Excel files grouped by manager? (ie:
Excel file called Manger.xls which includes only the data for their
respective employees).

Any assistance is much appreciated. Thanks.

 

[Ken Snell \(MVP\)]

You'll need to use separate queries to generate the records for each
individual manager, and then export each query separately.

This can be done by a variety of ways, ranging from manually creating the
separate queries and then using them in separate TransferSpreadsheet actions
(macro or VBA), up to using VBA code to generate the queries on the fly and
export them individually.

Which approach do you wish to try/use?

 

[Guest]

Hi Ken,

Thanks for your response. I would prefer to use VBA and try to automate the
process as much as possible.

As far as generating separate queries for each manager, does the manager
name have to be known criteria or can code be used to move through the
records and recognize when the manager name changes?

Thanks again.

 

[Ken Snell \(MVP\)]

The approach would not require you to "know" when a manager name has changed
in the data. Instead, one would use a query that gives you the unique
manager names, and then you filter the data for each manager name before you
export the data.

Here is code that will do what you seek ("air code" - not fully tested):

'Start of code
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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' 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
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.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.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & 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 of code


Let me know of errors / questions.

 

[Guest]

Thank you, Ken. I will give it a try.

The approach would not require you to "know" when a manager name has changed
in the data. Instead, one would use a query that gives you the unique
manager names, and then you filter the data for each manager name before you
export the data.

Here is code that will do what you seek ("air code" - not fully tested):

'Start of code
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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' 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
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.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.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & 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 of code


Let me know of errors / questions.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for your response. I would prefer to use VBA and try to automate
the
process as much as possible.

As far as generating separate queries for each manager, does the manager
name have to be known criteria or can code be used to move through the
records and recognize when the manager name changes?

Thanks again.

 

[Guest]

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " & "MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression 'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

Thank you, Ken. I will give it a try.

The approach would not require you to "know" when a manager name has changed
in the data. Instead, one would use a query that gives you the unique
manager names, and then you filter the data for each manager name before you
export the data.

Here is code that will do what you seek ("air code" - not fully tested):

'Start of code
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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' 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
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.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.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & 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 of code


Let me know of errors / questions.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for your response. I would prefer to use VBA and try to automate
the
process as much as possible.

As far as generating separate queries for each manager, does the manager
name have to be known criteria or can code be used to move through the
records and recognize when the manager name changes?

Thanks again.

:

You'll need to use separate queries to generate the records for each
individual manager, and then export each query separately.

This can be done by a variety of ways, ranging from manually creating the
separate queries and then using them in separate TransferSpreadsheet
actions
(macro or VBA), up to using VBA code to generate the queries on the fly
and
export them individually.

Which approach do you wish to try/use?
--

Ken Snell
<MS ACCESS MVP>


I have a table of employees and their managers. I need to send an Excel
file
to each manager to review the list of their employees. Is there a way
to
export the employee data to separate Excel files grouped by manager?
(ie:
Excel file called Manger.xls which includes only the data for their
respective employees).

Any assistance is much appreciated. Thanks.

 

[Ken Snell \(MVP\)]

What do you store in tblGlpRecertification in MgrLastName field to identify
the manager? If that is the last name, and that is what you want to use for
naming the exported files, then we can modify the code a bit. What I've done
below is to eliminate the DLookup step (not needed because you have the last
name already from your data table) and to delimit the manager's last name
with ' characters in the SQL statement that is used to filter the data based
on one manager's name:


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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr!MgrLastName.Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM tblGlpRecertification WHERE " & "MgrLastName
= '" & strMgr & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression
'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

:

< snipped >

 

[Guest]

That worked! The code really starts to make sense once you connect it to a
real-life situation. I can't thank you enough for all your help...take care.

What do you store in tblGlpRecertification in MgrLastName field to identify
the manager? If that is the last name, and that is what you want to use for
naming the exported files, then we can modify the code a bit. What I've done
below is to eliminate the DLookup step (not needed because you have the last
name already from your data table) and to delimit the manager's last name
with ' characters in the SQL statement that is used to filter the data based
on one manager's name:


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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr!MgrLastName.Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM tblGlpRecertification WHERE " & "MgrLastName
= '" & strMgr & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression
'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

:

< snipped >

 

[Ken Snell \(MVP\)]

Glad to hear it! You're welcome.

--

Ken Snell
<MS ACCESS MVP>

That worked! The code really starts to make sense once you connect it to
a
real-life situation. I can't thank you enough for all your help...take
care.

What do you store in tblGlpRecertification in MgrLastName field to
identify
the manager? If that is the last name, and that is what you want to use
for
naming the exported files, then we can modify the code a bit. What I've
done
below is to eliminate the DLookup step (not needed because you have the
last
name already from your data table) and to delimit the manager's last name
with ' characters in the SQL statement that is used to filter the data
based
on one manager's name:


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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr!MgrLastName.Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= '" & strMgr & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression
'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

:

< snipped >

 

[Guest]

Thanks for this post. It helped me acheive what I was looking to do!

I did modify it it a bit. Instead of it doing a excel workbook for each
employee I did it so that each employee has his/her own tab in one single
workbook. Now I just click a button have it created, put in an email and
send it off.

Here is what I did:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp,
"H:\Excel\" & Format(Now(), "mmddyyyy") & ".xls", False, strMgr

Instead of:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,

strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"

What do you store in tblGlpRecertification in MgrLastName field to identify
the manager? If that is the last name, and that is what you want to use for
naming the exported files, then we can modify the code a bit. What I've done
below is to eliminate the DLookup step (not needed because you have the last
name already from your data table) and to delimit the manager's last name
with ' characters in the SQL statement that is used to filter the data based
on one manager's name:


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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr!MgrLastName.Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM tblGlpRecertification WHERE " & "MgrLastName
= '" & strMgr & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression
'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

:

< snipped >

 

[dnr]

Thanks for the post - it has been very helpful. I was hoping you could help
me with one issue I am having. I get the same error a lot here is what is
says:

Microsoft Access was unable to append all the data to the table
The contents of fields in "X" records were deleted, and 0 records were lost
due to key violations.

It then goes into more detail.

I am wondering if it is doings this because some cells may be blank. How
can I deal with this error?

Glad to hear it! You're welcome.

--

Ken Snell
<MS ACCESS MVP>

That worked! The code really starts to make sense once you connect it to
a
real-life situation. I can't thank you enough for all your help...take
care.

What do you store in tblGlpRecertification in MgrLastName field to
identify
the manager? If that is the last name, and that is what you want to use
for
naming the exported files, then we can modify the code a bit. What I've
done
below is to eliminate the DLookup step (not needed because you have the
last
name already from your data table) and to delimit the manager's last name
with ' characters in the SQL statement that is used to filter the data
based
on one manager's name:


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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr!MgrLastName.Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= '" & strMgr & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,

I keyed the code into the program as follows:

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

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT MgrLastName FROM tblGlpRecertification;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("MgrLN", "tblMgrNames", "MgrLastName = " &
rstMgr!MgrLastName.Value)
strSQL = "SELECT * FROM tblGlpRecertification WHERE " &
"MgrLastName
= " & rstMgr!MgrLastName.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

I receive an error message at the line with DLookup -- Run time error
'3075': Syntax error (missing operator) in query expression
'MgrLastName=Abig
SR'.

Any ideas? Again, thanks so much for your assistance.

:



< snipped >