William Benson
vbacreations at gmail.com
Sat Jun 25 08:48:43 CDT 2011
Definitely dealt with it. First do whatever it takes to standardize in excel, loop files in folder; open each workbook and loop worksheets examining structure. Write field headers into an array or collection or a storage sheet. Any field names which are not standard (application.match(fieldname,fieldlist,false) results in error... need to be swapped for something applicable to all or else added to the list of applicable fields to every client (added to the list). Once you have a list of all field names and the consolidated list that they map to (most will be one to one but some will be many to one and some will be missing from most workbooks but will be inserted in the ones that are missing) then you make a distinct list of mapped fields and sequence alphabetically or however you like. Then go back thru every data sheet and put in a. Blank column in any sheet that is missing something which one or only a few others had. Now every sheet has all the same number of columns even tho some are named differently and might be out of order. About to take care of that. Loop thru sheets again and insert new row 1 and 2. That is where you will put a formula matching the fieldname below it to the list you made. Row 1 gets the match result (2, 1, 16, ... whatever sequence raw data is in). Then row 2 gets the mapped field name (use match and index or vlookup). Now delete original fieldnames in row 3. Sort on row 1 left to right then remove row 1. There is more to do but this will get you started. Bill Benson Owner VBACreations, LLC On Jun 25, 2011 8:36 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 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto: accessd-bounces at databaseadvisors.com] On Behalf Of 'Steve Goodhall' > Sent: Friday, June 24, 2011 10:52 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Automating Excel imports > > I give spreadsheets to clients for data entry, but I protect > everything that I don't want them to mess up. That said, it > generally takes me several iterations before they stop finding ways to > break it anyway. > > Regards, > > Steve Goodhall, MSCS, PMP > 248-505-5204 > > ----- Original Message ----- > From:Access Developers discussion and problem solving > To:"Access Developers discussion and problem solving" > Cc: > Sent:Fri, 24 Jun 2011 08:42:05 -0700 > Subject:Re: [AccessD] Automating Excel imports > > Duly noted! > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [1] > [mailto:accessd-bounces at databaseadvisors.com [2]] On Behalf Of Stuart > McLachlan > Sent: Thursday, June 23, 2011 1:20 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Automating Excel imports > > Finally, you've identified the real problem. > > Don't give clients spreadsheets for data entry. They *will* screw > them up. > Give them a some other tool such as a simple Access database with a > single > continuous > form bound to a single table. > > -- > Stuart > > On 23 Jun 2011 at 7:48, Darrell Burns wrote: > >> That's the problem...not all of the columns are formatted as text. > I >> created the template that way, but sometimes the client does a > paste >> and changes the format. >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com [3] > http://databaseadvisors.com/mailman/listinfo/accessd [4] > Website: http://www.databaseadvisors.com [5] > > -- > AccessD mailing list > AccessD at databaseadvisors.com [6] > http://databaseadvisors.com/mailman/listinfo/accessd [7] > Website: http://www.databaseadvisors.com [8] > > > Links: > ------ > [1] mailto:accessd-bounces at databaseadvisors.com > [2] mailto:accessd-bounces at databaseadvisors.com > [3] mailto:AccessD at databaseadvisors.com > [4] http://databaseadvisors.com/mailman/listinfo/accessd > [5] http://www.databaseadvisors.com/ > [6] mailto:AccessD at databaseadvisors.com > [7] http://databaseadvisors.com/mailman/listinfo/accessd > [8] http://www.databaseadvisors.com/ > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com