[AccessD] Automating Excel imports - How to do the basics

Brad Marks BradM at blackforestltd.com
Thu Jun 23 07:53:36 CDT 2011


Mark,

I have a lot less experience than others here in AccessD.  I have
exported files from Access to Excel and imported Excel files into
Access.  However, I have not yet experimented with initiating Excel
directly from Access.  Could you post the code on how to do this or
perhaps point me to a website that spells this out.  I would like to
learn more about this and it would be nice to have an example to start
from.

Thanks,
Brad 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
Sent: Wednesday, June 22, 2011 6:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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


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

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





More information about the AccessD mailing list