We use a product to create sales proposals and would like the ability to import manufacturers quotes into our proposals. Our product has this capability built-in but the data must be in csv format and to be easily importable using predefined maps, the csv file needs to be formatted with specific data in specific columns. The data we receive from manufacturers is either in excel or csv format and each manufacturer formats their data differently. We are in need of a macro/script that will reformat a file from a manufacturer into a csv file.
• Upon execution of macro, the user should be prompted to select the source file (an excel file or csv)
• User will also be prompted to select a manufacturer name from a drop down list (or other?). This list should be easily modified by a non-programmer (me)
• The name selected will be used to 1) populate the “Manufacturer” column in the source file, and 2) Select the appropriate mappings for file reformatting. Initially this will be a single name in the drop down list (“NetApp”) and there will be a single mapping.
o My thought would be to have different sheets in an excel file and each sheet would specify the required columns and column order for each manufacturer and if required, the data to fill empty cells. That way a new sheet could be added that would specify the mappings for each manufacturer
• After selecting the source file and Manufacturer from the drop down list, the macro/program should reformat the file as described in the attached excel file.