[AccessD] Automating Excel imports

Doug Murphy dw-murphy at cox.net
Wed Jun 22 18:47:13 CDT 2011


In access 2010 you go through the entire import wizard and when you get to
the last step it asks if you want to save the import steps. I am guessing
2007 is the same.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Wednesday, June 22, 2011 4:06 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automating Excel imports

Doug, how to create an Excel import spec in A2007? You used to be able to
save your spec in the import wizard prior to A2007, but I don't see that
function anymore.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Wednesday, June 22, 2011 3:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automating Excel imports

I am doing this in Access 2010 using TransferText with an import spec. I
bring the info into a temp table which is deleted and then recreated with
each import. The data is then moved to the appropriate table through an
append query.

Doug

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list