[AccessD] Import from Excel

Andy,

This maybe of some use to you?

http://www.excelyourbusiness.com.au/ms_access_page.htm#AccessImportFromExcel

regards
Darryl


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Thursday, 21 August 2008 1:20 AM
To: Dba
Subject: [AccessD] Import from Excel


I found the post below from Jim Hale in the archives. Hope you see this Jim,
but maybe others have done something similar.

I have a project which involves reading a variety of data into Access that
will be sent from different sites as Excel workbooks in differing layouts.
If I create links from Access to all of those workbooks I give myself a
maintenance issue because there will be many workbooks in many different
formats all of which would require different queries to read the data. Not
to mention the refreshing of so many links when I send the app out. I'm sure
it could all be done but messy.
So then I looked at reading the data from the Excel workbooks with code. As
far as I can see that will involve looping through a range handling cells
one at a time. I could parameterise this and make it quite elegant with just
one or two concise bits of code but won't it be slow? Is there another way
in code without doing it a cell at a time? Jim, if you see this how do you
do it? You said "I never link directly to a user provided table- I write
code to read the "records" and validate them as necessary", which sounds
just what I want to do but am I missing a trick, or does it all run pretty
fast anyway.


--
Andy Lacey
http://www.minstersystems.co.uk

I have run into cases where applications that create Excel files that in
turn are imported into Access treat numbers as text fields for some reason.
Since Excel shows it as text, Access also imports it as text. One solution
is to highlight an empty Excel cell and <paste special><value><add> onto the
text column. This has the effect of adding a zero (Excel treats an empty
cell as zero) and forcing Excel to change the text to a number. This also
cures the problem where some entries are numbers and some text (a user puts
an apostrophe in front of a "number" for ex).

Since Excel "data tables" are free form, i.e. the user can (and often does
insert rows, stick text comments in a number field, etc., etc., I never link
directly to a user provided table- I write code to read the "records" and
validate them as necessary. I write the raw data to an Access table then do
all my processing from there. I have found that links to Excel sheets are
relatively fragile. Excel sheets are easily corrupted, so I prefer to
transfer everything into Access ASAP.

Jim Hale


________________________________________________
Message sent using UebiMiau 2.7.2

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

This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.