Exporting multiple files to a template instead of a new file!

  • Thread starter Thread starter bilbo+
  • Start date Start date
B

bilbo+

Hi there, using the code below i am exporting several temp queries to new
Excel files, can anyone think of a way to export them to a template and
specific range and then save as a new file instead since I cant get it to
work... Its so that I can effectively 'paste' the info into a pre designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][, spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls", , H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
 
You can export all the temp queries to the same EXCEL file if you keep the
same name for the EXCEL file in each TransferSpreadsheet action step. If you
use the name of an EXCEL file that already exists, the TransferSpreadsheet
action will export into that file, and won't create a new one, so long as
you give it the correct name of the EXCEL file.

As for exporting to a specific range in the EXCEL file, see this article for
information on how you MIGHT be able to use the undocumented Range argument
for the export step:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

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


bilbo+ said:
Hi there, using the code below i am exporting several temp queries to new
Excel files, can anyone think of a way to export them to a template and
specific range and then save as a new file instead since I cant get it to
work... Its so that I can effectively 'paste' the info into a pre designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][,
spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls", ,
H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
Click to expand...
 
Hello, thanks for the prompt reply. What I really want to be able to do is
export the different tables to the same template, and then save it as A
SEPARATE file. So what i will end up with is say 50 excel spreadsheets, made
from the tempalte, with their relevant different details inside.

a bit of background info - what ive got is a table fill of students and
universities, what i need is an excel spreadsheet per university which has
their students inside. And this info needs to be added to a pre made excel
spreadsheet (which has other info in it)

Does that make sense?

any help greatly appreciated!

thanks,

Will

Ken Snell MVP said:
You can export all the temp queries to the same EXCEL file if you keep the
same name for the EXCEL file in each TransferSpreadsheet action step. If you
use the name of an EXCEL file that already exists, the TransferSpreadsheet
action will export into that file, and won't create a new one, so long as
you give it the correct name of the EXCEL file.

As for exporting to a specific range in the EXCEL file, see this article for
information on how you MIGHT be able to use the undocumented Range argument
for the export step:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

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


bilbo+ said:
Hi there, using the code below i am exporting several temp queries to new
Excel files, can anyone think of a way to export them to a template and
specific range and then save as a new file instead since I cant get it to
work... Its so that I can effectively 'paste' the info into a pre designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][,
spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls", ,
H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
Click to expand...
Click to expand...
 
If you export 50 differently named queries to the same EXCEL workbook,
you'll get 50 worksheets within that one workbook.

Your use of "template" -- are you wanting each worksheet to be based on a
single worksheet template?
--

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


bilbo+ said:
Hello, thanks for the prompt reply. What I really want to be able to do is
export the different tables to the same template, and then save it as A
SEPARATE file. So what i will end up with is say 50 excel spreadsheets,
made
from the tempalte, with their relevant different details inside.

a bit of background info - what ive got is a table fill of students and
universities, what i need is an excel spreadsheet per university which has
their students inside. And this info needs to be added to a pre made excel
spreadsheet (which has other info in it)

Does that make sense?

any help greatly appreciated!

thanks,

Will

Ken Snell MVP said:
You can export all the temp queries to the same EXCEL file if you keep
the
same name for the EXCEL file in each TransferSpreadsheet action step. If
you
use the name of an EXCEL file that already exists, the
TransferSpreadsheet
action will export into that file, and won't create a new one, so long as
you give it the correct name of the EXCEL file.

As for exporting to a specific range in the EXCEL file, see this article
for
information on how you MIGHT be able to use the undocumented Range
argument
for the export step:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

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


bilbo+ said:
Hi there, using the code below i am exporting several temp queries to
new
Excel files, can anyone think of a way to export them to a template and
specific range and then save as a new file instead since I cant get it
to
work... Its so that I can effectively 'paste' the info into a pre
designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][,
spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls",
,
H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Yeah basically I have a workbook where the first worksheet has some other
info in it and thi exprt data from access needs to go into columns G and H.
It also has a macro attached to it that will need to be on every spreadsheet
file. Does that make sense?

Thanks,


Ken Snell MVP said:
If you export 50 differently named queries to the same EXCEL workbook,
you'll get 50 worksheets within that one workbook.

Your use of "template" -- are you wanting each worksheet to be based on a
single worksheet template?
--

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


bilbo+ said:
Hello, thanks for the prompt reply. What I really want to be able to do is
export the different tables to the same template, and then save it as A
SEPARATE file. So what i will end up with is say 50 excel spreadsheets,
made
from the tempalte, with their relevant different details inside.

a bit of background info - what ive got is a table fill of students and
universities, what i need is an excel spreadsheet per university which has
their students inside. And this info needs to be added to a pre made excel
spreadsheet (which has other info in it)

Does that make sense?

any help greatly appreciated!

thanks,

Will

Ken Snell MVP said:
You can export all the temp queries to the same EXCEL file if you keep
the
same name for the EXCEL file in each TransferSpreadsheet action step. If
you
use the name of an EXCEL file that already exists, the
TransferSpreadsheet
action will export into that file, and won't create a new one, so long as
you give it the correct name of the EXCEL file.

As for exporting to a specific range in the EXCEL file, see this article
for
information on how you MIGHT be able to use the undocumented Range
argument
for the export step:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

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


Hi there, using the code below i am exporting several temp queries to
new
Excel files, can anyone think of a way to export them to a template and
specific range and then save as a new file instead since I cant get it
to
work... Its so that I can effectively 'paste' the info into a pre
designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][,
spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls",
,
H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Only way I know for doing this will involve VBA code to make a copy of the
template file, export a query into that copy, repeat for each query to be
exported, and then use Automation to open all the exported workbook files
and to create a new file and then to copy the spreadsheets from each of the
exported workbooks into the newly created workbook file.

As for exporting just to columns G and H, if the export involves contiguous
cells, the Range argument (the article that I linked to in my first reply)
MAY be useful. If you're not using contiguous cells, then the steps in the
first paragraph above would have to be revised to use Automation to write
the data into the worksheet cells (I have an article on my website, above
the one I linked to earlier) that shows how to do that.

How strong are your ACCESS VBA and EXCEL VBA skills?
--

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


bilbo+ said:
Yeah basically I have a workbook where the first worksheet has some other
info in it and thi exprt data from access needs to go into columns G and
H.
It also has a macro attached to it that will need to be on every
spreadsheet
file. Does that make sense?

Thanks,


Ken Snell MVP said:
If you export 50 differently named queries to the same EXCEL workbook,
you'll get 50 worksheets within that one workbook.

Your use of "template" -- are you wanting each worksheet to be based on a
single worksheet template?
--

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


bilbo+ said:
Hello, thanks for the prompt reply. What I really want to be able to do
is
export the different tables to the same template, and then save it as
A
SEPARATE file. So what i will end up with is say 50 excel spreadsheets,
made
from the tempalte, with their relevant different details inside.

a bit of background info - what ive got is a table fill of students and
universities, what i need is an excel spreadsheet per university which
has
their students inside. And this info needs to be added to a pre made
excel
spreadsheet (which has other info in it)

Does that make sense?

any help greatly appreciated!

thanks,

Will

:

You can export all the temp queries to the same EXCEL file if you keep
the
same name for the EXCEL file in each TransferSpreadsheet action step.
If
you
use the name of an EXCEL file that already exists, the
TransferSpreadsheet
action will export into that file, and won't create a new one, so long
as
you give it the correct name of the EXCEL file.

As for exporting to a specific range in the EXCEL file, see this
article
for
information on how you MIGHT be able to use the undocumented Range
argument
for the export step:

Using the Range Argument of TransferSpreadsheet when Exporting Data to
an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

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


Hi there, using the code below i am exporting several temp queries
to
new
Excel files, can anyone think of a way to export them to a template
and
specific range and then save as a new file instead since I cant get
it
to
work... Its so that I can effectively 'paste' the info into a pre
designed
excel file...

Thanks

Any help much appreciated!

Private Sub Command5_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

Const strQName As String = "zExportQuery"

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 GroupNameID FROM Customers;"
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("GroupName", "GroupNameTable", _
"GroupNameID = " & rstMgr!GroupNameID.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 Customers WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.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 [transfertype][,
spreadsheettype],
tablename, filename[, hasfieldnames][, range]
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,
strTemp, "C:\WBB\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") &
".xls",
,
H: G
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top