[AccessD] Automating Excel imports

Doesn't matter...my solution still stands.
Only limitation : 65,535 rows max in Excel 2003. 1048575 rows max in Excel
2007.


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Doug Murphy
> Sent: Wednesday, June 22, 2011 7:01 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Automating Excel imports
>
> 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?
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com