Export Table in Parts to Excel?

  • Thread starter Thread starter rishi
  • Start date Start date
R

rishi

hi there all
i am new to access and i need some of your expertise.

i have one table with the following columns:

ManagerNo
ClientName
NoOfEmployees
NoOfContracts

what i wiould like to do is export this table to excel. but the tricky
part is that i want each manager (there are about 15 some having more
than one client) to go to a separate workbook.

is this possible?

thanks

Rishi
 
I would sugest you simply create a query to filter the data for each manager,
and export them to excel. You could even use setup macros (Action: Output
To) and set it up to export, and even specify where you want them to be saved
to, if you prefer. OR, set it up to email the exported Excel file to the
manager with just one click.

I don't know how familiar you are with using Macros in Access, but they are
easy to use. I can be more detailed if need be.

Good Luck!
Stacy
 
Stacy said:
I would sugest you simply create a query to filter the data for each manager,
and export them to excel. You could even use setup macros (Action: Output
To) and set it up to export, and even specify where you want them to be saved
to, if you prefer. OR, set it up to email the exported Excel file to the
manager with just one click.

I don't know how familiar you are with using Macros in Access, but they are
easy to use. I can be more detailed if need be.

Good Luck!
Stacy
Click to expand...

Thank you for your help.
I am familiar with the macros that you mentioned.
I was just looking for a shortcut so that, when the number of managers
increase I will not have to do additional queries.

rishi
 
"rishi" wrote...
[...]
I was just looking for a shortcut so that, when the number of managers
increase I will not have to do additional queries.

rishi
Click to expand...

Rather than hard-coding multiple queries, why not write just *one*
with ManagerNo as a parameter?

hth,
John
 
If you want everything to be exported to different sheets of the same
workbook, the best solution is to do the export with OLE automation code.
The TransferSpreadsheet Action/Method will only export to a single sheet at
a time and the sheet will be named after the table or query being exported.
So to use this method, you would need a saved query for each manager. You
could automate this by reading the table containing all managers and create
a query for each manager, save it, and run it. As long as the file name is
the same, the sheets will all be added to the same workbook.

Either solution will take more than macros. You'll need to write VBA code.

You can create a code loop
 
Hi Rishi,

This can be done at the cost of learning some VBA. Probably the simplest
way is this:

1) I assume you already have a form that that the user will be looking
at when he/she wants to start the export process. If not, you need to
create one. I'll refer to it as frmXXX.

2) Put a commandbutton and a textbox on the form. Name the commandbutton
cmdExportToExcel, and set its Caption to Export. Name the textbox
txtManagerToExport.

3) Create a Select query based on your table, returning the fields you
need. In the ManagerNo column, in the Criteria row, put
[Forms]![frmXXX]![txtManagerToExport]
This makes it a parameter query that gets its parameter from the textbox
you have just put on the form. Save the query as qryExportManager

3) In the commandbutton's Click event procedure, write code like this.
Replace XXX with the actual name of your table:

Dim rsR As DAO.Recordset
Dim strWBK As String

strWBK = "D:\Folder\Filename.xls" 'filespec of workbook to create

'Open recordset with list of managers
Set rsR = CurrentDB.OpenRecordset( _
"SELECT DISTINCT ManagerNo FROM XXX;", dbOpenSnapshot)

Do Until rsR.EOF 'loop through the recordset

'set the textbox to the manager number
Me.txtManagerToExport.Value = rsR.Fields("ManagerNo").Value

'Export the query to a worksheet sheet with ManagerNo as name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryExportManager", strWBK, True, _
rsR.Fields("ManagerNo").Value

rsR.MoveNext 'next manager
Loop

'Tidy up
rsr.Close

If you want the user to be able to specify the name and location of the
Excel workbook, use the code at
http://www.mvps.org/access/api/api0001.htm.
 
Back
Top