The excel file has three sheets: one for data entry, another that formats
the
data into a report and the [[other which formats the same data into a
record
format which I want to export to Access. ]]

The obvious answer it to use your macro to build the sheet to support the
import into access. If you have fields in paragraph format, then CSV might
not be the way to go. Access can also import an excel spreadsheet.

--
Regards,
Tom Ogilvy



"WDR" wrote in message
...
Thanks Tom. Does it matter that the data in the sheet is within a certain
range? I don't want to export the whole sheet, just a range. If so, what
do
I need to do? If it matters, some of the fields are in paragraph form and
I
am using Excel 2000.

"Tom Ogilvy" wrote:

Have the user export the file as a CSV file, then import it into Access.

Worksheets(3).Copy
Activeworkbook.SaveAs Thisworkbook.Path & "\" & "Bob_" _
format(date,"yyyymmdd") & ".csv", xlCSV
ActiveWorkbook.Close SaveChanges:=False

--
Regards,
Tom Ogilvy

"WDR" wrote in message
...
Based upon my situation below what is the best way to export records
located
in a sheet to an Access format?

In my situation, there are multiple users (out in the field & not
connected
via LAN) that have a copy of the same stand alone spreadsheet file.
The
excel file has three sheets: one for data entry, another that formats
the
data into a report and the other which formats the same data into a
record
format which I want to export to Access. There will be approximately
six
records created daily by five users (30 new records daily).

My vision is that I want this routine to create an export file for
each
person. Each person will invoke this routine at the end of the day
and
then
they will email the
resulting export file to the office. All records will be deleted in
the
Excel sheet. The office secretary will then run a different routine
on
the
Access side to import each file into Access. Access will then
maintain
these
records.

If you have a better solution or any pointers in the right direction
will
be
gratefully received!

-Warren