[AccessD] Automating Excel imports

I was in a vaguely similar situation a couple of years back, when I had to
write a month-end process that opened ~100 pairs of spreadsheets (this month
and this month last year), grabbed some numbers from each pair, and created
a ~100-sheet workbook containing the consolidated results. The members of a
pair were identical, but the pairs themselves differed among one another. My
solution was to define ranges within the pairs, so that even if the location
and size of the ranges differed, I could still handle it by walking the
ranges and programmatically inserting the ranges into their destination
sheets on the result workbook. It definitely took a while to figure out how
to do it all programmatically, especially given that in each month old pairs
might be retired and new pairs created, not to mention that the algorithm
had to anticipate the changing of the year.

The first sheet of the result workbook consolidated values from the ~100
other sheets. (Each sheet represented the performance of an investment
fund.) By defining ranges and naming cells, I was able to do the
consolidation despite the differences in layout.

I don't know how much of this might apply to your situation. It rests on two
assumptions:

1. Each customer uses exactly the same format month to month.
2. Each customer is willing to have you take a template and define ranges
and cell names in it, then return that template for future use by the given
customer.

If these assumptions, hold, then you can fully automate the process despite
the variation from customer to customer. It will take some time to figure
out how to define the individual ranges and names so as to remain constancy
across all customers.

In my case, the formally manual process took two people about two weeks to
perform the process. My completed solution reduced this to less than 10
minutes, and required nothing more than entry of the current month-end date
and clicking the GO button. Developing the solution definitely took time,
but the client was more than pleased with the ultimate solution.

Arthur

On Sat, Jun 25, 2011 at 8:32 AM, Brad Marks <BradM at blackforestltd.com>wrote:

> All,
>
> This discussion about Automating Excel Imports has lead me to share our
> situation to see if anyone else has experience in how to best deal with this
> process.
>
> Our firm receives spreadsheets from about 100 firms (customers).  The data
> from these 100 firms is basically the same but there is variation from firm
> to firm.
>
> We receive one spreadsheet from each customer each month.
>
> There is a lot of variation in how the spreadsheets are defined.  For
> example the column names vary from firm to firm.
>
> Many of these spreadsheets are automatically generated by our customers.
>  For example, one firm may be pulling data from Oracle to build their
> spreadsheet and another firm may use an entirely different automated
> process.  The bottom line is that our customers are not manually entering
> their data.
>
> In order to bend over backwards to serve the customers, we try to make it
> as easy as possible for them to do business with us.
>
> This process has been in effect for several years.
>
> Currently the inbound spreadsheets are handled internally via a manual
> process.  This works, but it is very labor intensive.
>
> We would like to streamline this process.  I have started a project to pull
> the data from the customer spreadsheets and store this data in Access.
>  Currently the customer data is simply kept by storing the customer
> spreadsheets internally on a server.
>
> Trying to import (into Access) 100 spreadsheets that are all defined
> differently is becoming quite challenging.
>
> We seem to be caught between a rock and a hard place.
>
> Dealing with the customer spreadsheets as they are currently defined is
> difficult.  Asking our customers to change how they submit their data is not
> something that our Sales Department would endorse.
>
> I am curious if anyone else has dealt with this type of situation.  I
> realize that there is not an easy answer, but I might be able to learn from
> others who have faced this challenge.
>
> Thanks,
> Brad
>
>