[AccessD] Excel to Access

Since I spend a great deal of my time reading data into and out of Excel
sheets for reports, budgets, financial statements,etc. I've tried all the
various methods and developed code to use most of them. If you KNOW the data
will be in a standard format (generally untouched by human hands), linking
to sheets or ranges is generally easiest and simplest to implement. For
example, I receive many bank statements that are system generated into Excel
sheets. Since I can count on these to be consistent month to month linking
to the data generally works and is hassle free. 

If users are updating sheets, however, it is a whole different matter. The
ways to screw up Excel sheet data are almost infinite and everyone's
cautions about trying to build error checking safeguards should be heeded.
By linking the data I want to import to a hidden sheet that I control I can
use formulas to check for the most common errors. In this way I can assert
some degree of control over the data to be imported. The error checking can
be as simple or elaborate as needed for a particular case and can be divided
between using Excel formulas and Access routines to share the data
validation load depending upon which makes the most sense in any given case.
Does this catch all errors all the time? Of course not. However, I can say
that I've used this technique the last four years to run an Excel/Access
profit planning system where 200 Excel budget templates were distributed to
store managers most with only modest Excel skills. The hidden sheets from
the completed templates were loaded into Access without problems. I DO load
everything into a temp table that is identical to the hidden sheet. This
serves two purposes. First, I can more easily track problems. If a
discrepancy exists I can immediately compare the Access table to the hidden
table to determine if the problem is between Excel and Access or after
Access has retrieved the data. Second, I find it is easier to process the
data when it is wholly within the Access environment.

BTW using code to load data from (or to) Excel sheets it is relatively easy
to develop it in such a way that the Excel user can run the code from a
button on the Excel sheet to load the data into Access. Conversely the code
can be run from Access to load the data to and from Excel. The former is
great when the user doesn't know (or care) about Access but needs to perform
the transfer. The latter is great for batch transfers when many files need
to be loaded. I usually use the latter while I am developing. When I am
confident the process is stable (or I get bored having to routinely run the
process) I will place the code into Excel for the user to run.

Jim Hale

-----Original Message-----
From: Reuben Cummings [mailto:reuben at gfconsultants.com]
Sent: Wednesday, December 07, 2005 10:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel to Access


How could I go about exporting specific portions of an excel spreadsheet to
a CSV from Access?

I would prefer to have them open the Access app and click a button to bring
the data in.  The function in Access would send the data from the specified
ranges (which would be hard coded for now) to a CSV, import that to a table,
and then format and place the data accordingly.

Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
> Sent: Wednesday, December 07, 2005 10:16 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Excel to Access
>
>
> Reuben,
>
> Excel is fraught with danger when used directly, ESPECIALLY when
> it is hand
> edited.  The reason is that the data can change data types down a column,
> let's say it started as a number, but they then cut and pasted something
> that Excel thinks is a string (but looks like a number).  When you link to
> that sheet, you will end up with #error or something similar scattered
> through your data.  If you must do this, then immediately export
> the data to
> a CSV file.  The reason for doing that is that a CSV file has
> nothing in it
> indicating what the data actually is, thus Access guesses when you set up
> the import.  Further you can then specify each field intentionally if you
> need to do so.
>
> So export to CSV
> Import the csv data to a temp table
> Work from that temp table.
>
>
> John W. Colby
> www.ColbyConsulting.com
>

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.