[AccessD] Automating Excel imports

Then you should be using TransferText to do it NOT EXCEL.

At 06:33 PM 6/22/2011, you wrote:
>Date: Wed, 22 Jun 2011 16:01:28 -0700
>From: "Doug Murphy" <dw-murphy at cox.net>
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Automating Excel imports
>Message-ID: <00c101cc3130$51f51830$f5df4890$@cox.net>
>Content-Type: text/plain;       charset="us-ascii"
>
>In my previous response I should have said the data we get is in comma
>delimited text files.
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
>Sent: Wednesday, June 22, 2011 3:28 PM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] Automating Excel imports
>
>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