Advice on Exporting to Excel from Access

[Guest]

Hi, I've been doing some hunting on line but this is new to me and I'm
wondering if anyone has any advice before I tackle this.

I have a User who currently works in a number of large Excel Spreadsheets
that she distributes regularly to the company. She also exports the data
from her spreadsheets into Excel forms that other staff must then fill out
remaining data on.

I have built a database that houses all of this user's data plus other
user's related data. There is header info (main table/form)and then each
specific job component (which would equal one Excel spreadsheet) is in a
subtable(s)/subform(s)

The User is willing to now enter the data into Access but would like to
continue using Excel for the rest of her work (read: is insistent upon)

I know that I will need to build a query for each spreadsheet and Excel form
that needs to be exported to. I've never done this before and am struggling
with how to make it the most functional. Do you think that if I built a form
that has a bunch of command buttons - each one to run a query to export -
that that would be user friendly? Do you see any potential problems with
building it this way? Are there any problems I should be watching out for
with exporting to Excel?

I really appreciate any tips! Thanks!

 

[Guest]

I have had the same issue with users. Here are some ideas.
Try to avoid allowing them to import data from the spreadsheets back into
Access. This is not always possible, but be sure they use spreadsheets that
have the correct field name and formatting.

To make it easy to export data to Excel spreadsheets, I would suggest a list
box or combo box rather than a button for each. The problem with the buttons
is every time you need to add a new export, you have to redesign your form.
I would suggest a table with two fields - the query or table name and a
description so it will be obvious to the user what the export is. Then use
that table as the row source for the control. I usually use a multi select
list box so the user can run multiple exports at the same time. It is a
little more code intensive, but more user friendly.

Also, it is a good idea to allow them to determine the destination path and
file name. You can do that with the common open file dialog. Here is a link
that has the API code to do that:

http://www.mvps.org/access/api/api0001.htm

You will notice that using this code, you will have the ability to identify
a default path and file name. Another idea would be to expand the table
mentioned previously to include a field to store the default path and file
for each query and use that to indentify the default for the dialog.
Then, just use the TransferSpreadsheet method to export the data.

Good Luck