[AccessD] Automating Excel imports

If all are text when you look at the fields in Excel, what I would
definitely do is use VBA automation to create an instance of Excel, open the
file, save it as CSV, and bring it into Access as a text file with
transfertext and an import specification. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Wednesday, June 22, 2011 6: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