[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