Import from Ordermentum to Excel (Pablo) -- 2

Macro: ORDERMENTUM_IMPORT Purpose: To open a file exported from the ordering website and populate the the excel order spreadsheet with quantities matched to the product codes for matching customers. Pre-processing: Prior to populating the spreadsheet, a number of checks should be performed on the exported data. Any negative results of the checks should be communicated back to the user via a dialog box. Check 1: Check for Consistent delivery dates - All delivery dates should be for the same date and that date should be the date that is named in the worksheet title. If a negative result is found, the import operation should cease. The dialog box is to have the working "One or more delivery dates in the exported file do not match this worksheet. Please check the exported data." Check 2: Check for duplicates. If there are two instances of the same customer with the exact same order, the operation should be paused. The dialog box should bring this occurrence to the attention of the user with two options Continue or Cancel. Passing the above two checks, the import process can begin. If a filter is applied to the worksheet of interest (or Master worksheet) the filters should be removed. Consideration should be given to speed of the process. As with previous macros, it may be more expedient to separate the worksheet into its own workbook before populating. Process Checks: Whilst processing the data during the populating stage, the following checks should be carried out - Check 1: The "State" field should be checked (column D). If the state says "Cancelled", the data should not be imported. Any customers with Cancelled orders should be shown in the dialog box at the end of the import. Check 2: New Items - If an item exists in the import data that is not in the spreadsheet, the item code and description should be added to the appropriate section of the worksheet. The populated cell for that item should be highlighted in red. The item should also be added to the Master worksheet. It is important that the totaling formulas a tops of the columns are not broken with the addition of new rows. Consideration should be give to the case where the item code has a supplier prefix that is not yet present in the worksheet or master. This has caused a fail in the past. One way to handle would be to not import the item and show the details of item and customer in the dialog box at the end of the process. Check 3: New Customers - If a customer in the import data is not yet defined in CUST and Shortname worksheets, it should simply be added to a new column and highlighted in red. Details should show in the dialog box at the end of the process. Successful Import: At the end of the import, if nothing else to show in the dialog box, it should show the message "Import Completed Successfully". Amendment1: New customer - The new columns should be placed at the end prior to the BLANK column. The cell should not be highlighted in red but rather the same blue as the other column headers so it can be cleared and repopulated without accidentally doubling the data Amendment 2: When importing data into a cell, if the cell already contains data it should be cleared of it. However, if a data file contains two orders for the same item placed by the same customer e.g. a customer orders 1 loaf of bread and then realises they need 2 more so the place another order.. the populated cell should contain the sum of those items from the file.