[AccessD] Access To Excel via a CSV File

And just to chime in here, you can also read and write to an Excel
spreadsheet (.xls format) using ADO (you can do the same with a .csv
file too).  Allows you to query against it too, just a matter of setting
up the connection object. (And there is a trick, you can make the first
row data, or field names, another property in the ADO connection
object).

Drew 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
Gross
Sent: Thursday, June 05, 2008 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access To Excel via a CSV File

Hi Max,

I do a lot of Access to Excel.  The only reason I would go to a CSV file
first is because there is a limit to the number of rows that you can
send to Excel - it is somewhere around 4,000.  If I know I am going to
hit that limit I go to CSV first, then open it in Excel and do the
totals, formatting, etc.  I leave Excel open and it is left to the user
to do the saving as xls.  I always keep Excel hidden until I am done so
that users don't start clicking away as data is transferring.

Also, I never use transferspreadsheet.  I always step through a
recordset and transfer data row by row.  It is quick and gives me much
more control.
For instance, date values have to be sent to Excel already formatted.
If you don't format them first they will send as integer and it seems
that no amount of formatting in Excel will bring them back around to
dates.

Here is some sample code, watch the word wrap.  The routines for getting
Excel open comes from Dev Ashish's site:

The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.