[AccessD] Automating Excel imports

I would open it as an ADO recordset and import it that way.
You do not need to deal with the TransferSpreadsheet method
and its issues that way.

I do it this way 99% of the time.


At 06:33 PM 6/22/2011, you wrote:
>Date: Wed, 22 Jun 2011 15:27:43 -0700
>From: "Darrell Burns" <dhb at flsi.com>
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Subject: [AccessD] Automating Excel imports
>Message-ID: <00f201cc312b$9ad7a400$d086ec00$@com>
>Content-Type: text/plain;       charset="us-ascii"
>
>My client needs to frequently import batches of customer data into the CRM
>database I built for them in A2007. I created an Excel template which they
>use to supply the data, and I have an automated process that reads in the
>batch and loads the database. This is the command I use to do the import:
>
>DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, BatchTable,
>Import_File, True, CellRange
>
>All of the fields in BatchTable have a data type of 'text'.  The problem I'm
>running into is that TransferSpreadsheet barfs if any of the zipcodes in the
>spreadsheet are formatted as numbers. Before A2007 I used to use an Import
>Spec, which would circumvent this problem. For some unfathomable reason,
>this feature no longer exists (that I know of). If this was a one-time
>import I could deal with it by manually importing into a new table and then
>copying the data into BatchTable, but this is a recurring event.
>
>Any advice on how to deal with this issue?

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com