[AccessD] Import from Excel

Great, thanks a million Darryl

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

>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>Darryl Collins
>Sent: 21 August 2008 03:14
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Import from Excel
>
>
>
>Andy,
>
>This maybe of some use to you?
>
>http://www.excelyourbusiness.com.au/ms_access_page.htm#AccessIm
>portFromExcel
>
>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.
>
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com 
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>