Darrell Burns
dhb at flsi.com
Wed Jun 22 18:55:34 CDT 2011
That's not an import spec, though. It just saves the steps. Csv isn't the solution either because there are commas in the data. I didn't want to ask the client to save as tab-delimited...they might screw it up. I'm gonna try linking instead of importing. I think I can make that work. Thanx! -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy Sent: Wednesday, June 22, 2011 4:47 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Automating Excel imports 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com