TransferSpreadsheet

  • Thread starter Thread starter GLW
  • Start date Start date
G

GLW

I have a table with the following columns:
Mgr, Rep, RepNum, HireDate, TermDate.
I need to export this to an Excel workbook with a seperate
worksheet for each manager. I know I can create queries to
filter the table for each manager and then run a
docmd.transferspreadsheet for each one but there must be a
more efficient way.
 
GLW said:
I have a table with the following columns:
Mgr, Rep, RepNum, HireDate, TermDate.
I need to export this to an Excel workbook with a seperate
worksheet for each manager. I know I can create queries to
filter the table for each manager and then run a
docmd.transferspreadsheet for each one but there must be a
more efficient way.
Click to expand...

You could create a query called, say, qryScratch, and then run some code
along these lines:

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qry = db.QueryDefs("qryScratch")
Set rs = db.OpenRecordset("some query that gives you a list of
Mgr's",dbOpenForwardOnly)

Do Until rs.EOF
qryScratch.SQL = "SELECT * FROM MyTable WHERE Mgr = " & rs!Mgr
DoCmd.TransferSpreadsheet 'To export qryScratch, I can't remember
the syntax
rs.MoveNext
Loop
 
Brian said:
You could create a query called, say, qryScratch, and then run some code
along these lines:

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qry = db.QueryDefs("qryScratch")
Set rs = db.OpenRecordset("some query that gives you a list of
Mgr's",dbOpenForwardOnly)

Do Until rs.EOF
qryScratch.SQL = "SELECT * FROM MyTable WHERE Mgr = " & rs!Mgr
DoCmd.TransferSpreadsheet 'To export qryScratch, I can't remember
the syntax
rs.MoveNext
Loop
Click to expand...

Looks like you did it exactly the way the OP said they already knew.

What do you mean by efficient <g>? You cannot simultaneously read
multiple arrays into multiple worksheets, so you will need to do some
kind of 'looping'.

Jamie.

--
 
Back
Top