[AccessD] Automating Excel imports

Just initiate an Excel instance from within Access, Open the workbook,
determine the sheet, get the last used row, determine the zip code column
and starting row number, and then iterate thru each cell placing a single
quote in the cell:
For i = RowStart to LastRow
oSht.Cells(I,ZipColNum).value = "'" & oSht.Cells(I, ZipColnum).Value
Next

There is no way to do a mass i.e. range-based conversion from numbers to
text.
You can do a mass conversion from text to numbers however.


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