Automating Excel import to Access with dynamic columns

Question

I maintain a inventory/price database of items from many different manufacturers. When we get price updates, manufacturers send out a new Excel document. Unfortunately these Excel documents differ from manufacturer to manufacturer, and they do not have standardized labels and formatting for their sheets (even from the same manufacturer).

I want to have a form so my employees can import new pricing and new part numbers into our database, and delete old part numbers. I only want old part numbers deleted if they do not exist in a new price list (WITH EXCEPTION, SEE LATER), and if I have 0 in stock.

Right now I've done a LOT of VBA work in Excel to automate formatting and labeling of the Excel price lists. This works fairly well most of the time, except when manufacturers make changes to their layout. All the same, right now we make the Excel sheets "fit" my Access database before I import manually with hand-written queries.

A little information: All of my PartNumbers are unique. Some data I have is entered in by hand, and I need to retain this data when I update pricing and other information (for example PageNumber may be different in this year's catalog, but my existing Description and SalePrice need to remain in tact.)

Here are the issues I am constantly up against: Price lists I recieve are always different. Some sheets may have PartNumber, Price, StandardPack. Some other sheets may have PartNumber, Price, Description, UMRP, PageNumber. Other sheets may have a lot of irrelevant data like Weight that don't pertain to my system. I need a single form/query that can import dynamic data like this into a single table.

Some manufacturers have different price lists. I have a list of DealerNet pricing with other information like Description PageNumber etc. Then I have a separate overriding sheet with a SalesDeal price and StandardPack information for items we buy in quantities. These two price lists will have completely different formatting and column labels, for reasons that I can't explain. This is the exception where when I'm just updating my SalesDeals, I don't want other part numbers from this manufacturer deleted. This can be done with a MsgBox popup.

Some price lists may have formatting that needs to be fixed. The problem with this is, sometimes a manufacturer changes their list and there's no way my employees understand VBA well enough to fix this if they needed to. Some of the data may be split into subcategories. Right now I fix this in Excel VBA before importing to Access.

Subcategories:

Some price lists may have finishes separated from the part number, and I would need a separate line for each individual finish. Again at the moment this is fixed in Excel VBA before importing to Access, but sometimes it breaks if a manufacturer makes changes to their price list.

Seperate Finish Column:

I suppose my biggest question here is, how do I have a single form import Excel sheets that may have different columns, into a single table? Is it best to continue normalizing my Excel sheets before import? And by normalize, do I just need to have the existing columns renamed to fit in my table, or does my Excel sheet need to have all of my Inventory table columns present before importing?

Answer 1

I have worked on some similar projects, so I understand a bit of what you are describing. Let me start with some principles that I try to keep in mind with this type of project, and then some practical tips you may find helpful.

First, let's take a minute to step back and consider what we are automating. Generally, the purpose of automation is to save time and/or reduce a complex task into one that can be performed by less skilled workers. The trade off is that automation requires a time and skills investment.

When I evaluate a potential automation project, I try to get as much of the bigger picture as I can. In this project I would be asking myself the following questions:

• How often do we receiving pricing updates?
• How many vendors am I working with?
• What elements (data columns) are most consistently the same across the vendors?
• How much of my import logic can I reuse/share between vendors?
• How confident am I in the reliability of the data I am receiving?
• How often am I adding/removing vendors?
• How much time and effort do I estimate it will take me to automate this?

Just as a case in point, I once spent several days building VBA logic that would parse out text and objects from pdf pages, determining fields by coordinate position and sequence. It was really complicated! But in that case we were importing tens of thousands of pages of valuable data, so it made sense. For a smaller project it would have been ridiculous to spend that much time on it. :-)

Then based on this analysis I would try to determine the best way to automate the greatest amount of the process with the least development complexity required. For some vendors you may need to do some initial cleanup/restructuring in Excel, but you may be able to import most of the others without any additional hand editing in Excel. You will just have to decide where that balance is for you.

A Practical Approach

My recommendation on this project would be to focus on a single generic import routine in VBA that would parse and import the pricing files. I would create alias options for the column headers that would allow you to use the files directly from the vendor. This could be done through a Select Case statement in VBA (example below) or through a alias lookup table.

For Each Col In rst.Columns
    Select Case Col.Name
        Case "Price", "Pricing", "Cost"
            strPriceCol = Col.Name
        Case "ID", "Code", "SKU"
            strIDCol = Col.Name
        ' etc...
Next Col

The advantage of a lookup table is that you (or other users) can add column name equivalents without needing to change any VBA code. Whether that makes sense will depend on how often you need to edit the aliases.

In your import form, the first thing you will want to do is link to the Excel spreadsheet. I would do this dynamically through VBA as a linked table with column headers so you can keep the processing logic in one place.

As you loop through the records in the vendor pricing recordset, you can add new pricing records to the master list, update existing ones, or delete unused ones. Keeping that logic in VBA with lots of comments makes it easy for you (or others) to understand what is going on. (As compared to lists of append, update and delete queries that only apply to specific vendors.)

Taking this approach will allow you to add new pricing spreadsheets from new vendors with a minimal amount of adjustment to your code. (Maybe even none, if you already have matching aliases defined.)

If you have vendor-specific processing, such as sections with different types of products (such as your subcategories image above), you could either have the user select the vendor as part of the import, or find something consistently unique about the file layout that would let you determine this automatically.

To make things even more robust, you may want to have some sanity checks involved. For example, you could parse the pricing into an intermediate table, link that to your current pricing, and present the user with a report that previews the pricing changes that will take place. This would give them a chance to confirm the changes before making the update.

Lots of options and ways you could approach this, but hopefully this gives you some additional ideas as you go forward!