VBA to update Products Table from excel file

[Mclaren]

Hello all,

Each day we receive a report (MM.xlsx), listing all products available from our manufacturer. The file often has some new products, some amended product descriptions and some products have been deleted from the file compared to previous files. The list is very long, approximately 13000 products.
I would like to be able to update my products table based on any changes in the new file using a form with an update button.

When clicking the button I would like the following to take place:

• ask user to select an excel file to compare current products with those in the excel file.
• using code, insert any new products to my table
• mark any products in my table as not available (uncheck the OnMM field in the products table), if the product is no longer on the manufacturers list (Not deleting from my table)
• Make changes to the product description if any changes exist.

I have attached example files with examples of each of the above points.

I must add the excel file is generated from SAP so should never change.

Any help or pointer in the right direction will be appreciated.

[John_G]

Hi -

One question - do you produce client orders and invoices based on what is in the products table? If so, does the order part of your database contain links to the products table?

In other words - Suppose a client today orders Widget X , with description Y. Then your supplier changes the description of Widget X, to Description Z, so you update the products table.

Then next week the boss wants to know what that Client ordered - what description of Widget X does the boss get?

Just a thought.

John

[Mclaren]

Hello John,

No, not in this database. the database is used for recording transactions outside of SAP, it used more for tracking stats etc...

Normilasation is not required on product description or on many of the other fields in the excel file. Annother example of one of the other fields in the excel file is manufacturing plant, this may change based on business requirements. (i must add, the Manufacturer is alsa part of our company)

So if Widget X is made in plant A and then later is moved to be manufactured in plant B, I lookup the value of the Plant and record that in other tables as required thus keeping both old and new values in other tables.