[AccessD] Automating Excel imports

Interesting problem. I have not yet come across this issue, but your message
suggests that I might soon receive a phone call from one client in
particular, and hence extra billable hours. In anticipation of this event, I
shall investigate -- pre-emptive strike, as it were. I'll report back on
this thread with results, either successful or failed.

A.

On Wed, Jun 22, 2011 at 6:27 PM, Darrell Burns <dhb at flsi.com> wrote:

> 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?
>
>