Some Microvellum Users build projects in an Excel spreadsheet instead of creating the project and adding the products inside Toolbox. Microvellum can import an Excel project spreadsheet. It results in a project that contains all the necessary elements for creating manufacturing and reporting data.
The file attached to this article is an example of the contents and formatting required for the source import file. You may use any Excel commands, controls, or formulas when working with this source file.
There are a few requirements for this import process to produce the necessary data within Microvellum.
Worksheets (sheet tabs)
ORDER. This worksheet contains the source datasheet for the project. This data is sent to the sheet "Order Data." Think of this sheet as the user interface for the project import utility.
Enter all the products to be included in the project here, along with the prompt values that you want to be set for the project that deviate from the prompt default. The headings in columns A – P cannot be changed. Starting at column Q, you define the prompts that you intend to be set to a value that is different than normal in the imported project. To get the prompts to match, you must first add them to column A in the sheet Mapping.
Cell A1: Project Name. Enter the project name for the import project. (Required)
Column A: Product Quantity. Enter the product quantity for each item in the project. (Required)
Column B: Product Description. Select a product from the dropdown list of products. This list is an external reference to the items located on the sheet "Products." The reason for the dropdown list is that the product name in this column is case sensitive and must be spelled as it is in the matching product library. When the Import function is activated, the product is added to the project if a match is found between this name and an identical name in the library. (Required)
Column C: Product Width. Enter the product width for that product. (Required)
Column D: Product Height. Enter the product height for that product. (Required)
Column E: Product Depth. Enter the product depth for that product. (Required)
Column F: Spec Group. Enter the name of the spec group assigned to that product. (Required)
Column G: Comment. Enter product comments as needed. (Optional)
Column H: Parent1. Enter the location or room name to which the product belongs. (Optional)
Columns I – P: (No Longer Used)
Columns Q – BZ: Prompt Names and Values that differ from the default library values that you want automatically populated in the new project. The prompt names on row 3 are linked to rows 17 and greater on the sheet "Mapping." They are case sensitive, must contain the same underscore characters as in the library, and they must be spelled the same. (Optional)
PRODUCTS. This worksheet contains the source data for the dropdown lists on the Order sheet. The Microvellum default template file contains a list of products on this sheet. You may modify this list of products to match your library product names.
Column A: Enter the list of products that will display in the dropdown lists on the sheet "Order." The names here are case sensitive and must be spelled exactly as it is in the matching product library.
JOB DATA. The Import function accesses data on this sheet. It references the source data sheet "Order" for the project name. You will not need to make modifications to this sheet.
Cell A1: The text string "Job Name" identifies the data in the cell to the right.
Cell B1: This cell contains an external reference to the project name on the sheet "Order."
ORDER DATA. The Import function accesses data on this sheet. It references the source data sheet "Order" for the product data. You will not need to make modifications to this sheet. Just confirm that there are enough rows on this sheet to match the products entered in the Order sheet. If you notice there are not enough, simply copy the formulas down from the previous row into the new rows.
MAPPING. This sheet contains the Prompt Names and Values that differ from the default library values that you want automatically populated in the new project. To get the prompts to populate correctly to the sheet "Order," you must first add them to column A in this sheet.
Rows 1 – 6: Prompts displayed in red are required and may not be modified.
Rows 7 – 8: Prompts displayed in yellow are optional.
Rows 9 – 16: Prompts displayed in blue are no longer used.
Rows 17 and greater: Prompts displayed in white are prompt names and values that differ from the default library values that you want automatically populated in the new project. The prompt names on row 17 and greater are linked to those on the sheet "Order" columns Q and greater. They are case sensitive, must contain the same underscore characters as in the library, and they must be spelled the same. They may be on this sheet as needed. Make sure that name is displayed in the corresponding column in the sheet "Order." If not, add an external reference on that sheet to the correct cell here.
GLOBAL VARIABLES. This sheet is to support backward compatibility to v67. Version 7 import files do not include this sheet.
PROJECT VARIABLES. Datasheet used in the import process for updating various data to the factory database. The data includes User Defined Project Properties (UDPP), global variables, project wizard, and door wizard data. It does not create or add data to a UDPP record if it does not already exist.
Column A: Variable Type. Enter one of the following - Project Property, Global Variable, Project Wizard, or Door Wizard. The order of the variables in the list is not important, and you may assign different values using the same variable name in different spec groups. It is possible to modify any of the existing User Defined Project Properties, and 11 of the Project level properties using the Project Property variable type. The 11 project-level properties are Architect, Contractor, Draftsman, Estimator, GeneralContact, JobAddress, JobEMail, JobFax, JobNumber, JobPhone, ProjectManager.
Column B: Variable Name. Enter the name of the variable exactly as it appears in the target file (global, project wizard, door wizard, or project properties). It is case sensitive, and you must include any underscores. They must match the names in the target spec group files.
Column C: Value. Enter the value you want to assign to the target variable defined in that row.
Column D: Specification Group. Enter the spec group to modify, or leave it blank to update all spec groups.
Once this file is configured as needed and saved to a known location, you are ready to import the project. Click "Toolbox Setup > Import XLS File" and select the XLS file from your known location.