This article explores the structure and layout of all major Microvellum workbooks, defines the functions and purposes of each of the respective column in the workbook, and offers a summary of various aspects of the different workbooks.
Major Microvellum Workbooks
Major workbooks within Workbook Designer, in the case of this article, are defined as the workbooks that make up one's specification group. These workbooks contain the vast majority of data within one's library and are broadly considered to be at the top of the spreadsheet hierarchy of the software. (For more information about Workbook hierarchy and the various workbooks, visit the following article).
These workbooks include:
F= Factory
H= Hardware
D= Door Wizard
G= Global
W= Project Wizard
M= Materials
E= Edgeband
The G! Workbook
The structure of the G! (Global) workbook is not present in this article, as the entirety of its structure, function, and a detailed explanation of Global Variables can be found in this article.
General Workbook Structure
Workbook spreadsheet structure is generally fairly uniform across the various different sheets and workbooks, with different specifics depending on the specific data contained within. While not identical, the general column layout in the table below is the standard within the data of the workbooks:
Item | Notes |
01 | Columns B thru G are the Global Categories. |
02 | Columns J thru V are the Global Variables. |
03 | Columns AD and up are the Lookup Tables. *Subject to changing to second worksheet |
04 | Cell A1 indicates the Library Version |
05 | Cell D1 indicates the Library Reference Name & Version From A1 |
06 | Column A is where your Global Variables Tab Names are stored. |
07 | The 1st Tab Name in column A (cell A2) has an Index of 0, and the 2nd tab name down has an Index of 1 and so on. |
08 | These Index Numbers in Column G and are linked to the Tab Names in column A. |
09 | The Category Names in Column D are linked to the Index Numbers in Column G, which controls what Global Tab the Category Name Appears. |
10 | Column B is the Unique Link I.D. that is assigned to All Global Category Names in Column D. |
11 | Column C is the Unique Link I.D. Category that the Global Sub-Category Names in Column D belong to. |
12 | Column D is where the Global Main & Sub-Category Names are stored. |
13 | Column E is the Category Level. 1 = Main or Parent, and 2 = Sub-Category or child. |
14 | Column F controls whether the Global Category Names are visible in the User Interface. 1 = Visible, and 0 = Invisible. When left blank, it defaults to visible. |
15 | Column G is the Tab Index that the Global Category Names reside. |
16 | Columns H & I are not used currently. |
17 | Column J is the unique Link I.D. that is assigned to the Global Variable Names (Prompts) in Column L. |
18 | Column K is the Link I.D. Category that the variable names in Column L belong to. |
19 | Column L is where the Global Variable Names are stored. |
20 | Column M is the Global Variable Value. (can be hard #, word, text string or formula, lists, etc.) |
21 | Column N is the Control Type. (See documentation on Control Types) |
22 | Column O is the Help String (comments) that can appear in the User Interface. |
23 | Column P is the Validation Code |
24 | Column Q is the Combo Box Text (Choices). Items are separated by a pipe | symbol. |
25 | Column R is the Qty Value |
26 | Column S controls the Prompt Value Color. Colors are 1 thru 9 (See Colors in Help Guide). |
27 | Column T is unused currently. |
28 | Column U is the Help Picture File Name. Program will search in the Common Microvellum Data\Imperial Master\Graphics\Global Images\ |
29 | Column V controls if the Variable Name (Prompt) is visible in the User Interface. 1 = Visible, and 0 = Invisible. |
30 | Columns W thru AB are not used currently. |
31 | Column AC contains info (Help Text) in regards to the Lookup tables contained in Columns AD and so on. NOTE: Libraries released after summer 2019 have their look up tables moved to a separate worksheet. |
F Workbook
F! is the Factory Workbook, containing the standardized and factory default settings of the current Foundation Library.
Column | Description |
A | Column A: Cell A1 is the library name and version number. Cell A2 and below are used for the Factory Variable Tab Names. The 1st Tab Name in column A (cell A2) has an Index of 0, and the 2nd tab name down has an Index of 1 and so on. The Index Numbers in Column G and are linked to the Tab Names in column A. The Category Names in Column D are linked to the Index Numbers in Column G, which controls what Global Tab the Category Name Appears. |
B | The Unique Link I.D. that is assigned to All Global Category Names in Column D. |
C | The Unique Link I.D. Category that the Global Sub-Category Names in Column D belong to. |
D | Where the Global Main & Sub-Category Names are stored. Cell D1 indicates the Library Reference Name & Version from A1. |
E | The Category Level. 1 = Mail or Parent, and 2 = Sub-Category or child. |
F | Controls whether the Global Category Names are visible in the User Interface. 1 = Visible, a 0 = Invisible. |
G | The Tab Index that the Global Category Names reside. |
H-I | Not currently used. |
J | The unique Link I.D. that is assigned to the Global Variable Names (Prompts) in Column L. |
K | The Link I.D. Category that the variable names in Column L belong to. |
L | Where the Global Variable Names are stored. |
M | The Global Variable Value. (Can be hard #, word, text string or formula, lists, etc.) These MUST be defined within the workbook and should match the name in column L. |
N | The Control Type. (See Reference: Control Types and Other Prompt Properties). |
O | The Help String (comments) that can appear in the User Interface. |
P | The ValidationCode. |
Q | The Combo Box Text (Choices). Items are separated by a pipe | symbol. |
R | The QtyValue. |
S | Controls the Prompt Value Color. Colors are 1 thru 9 (See Colors in Help Guide). |
T | Not currently used. |
U | Is the Help Picture File Name. Program will search in the Common Microvellum Data\Imperial Master\Graphics\Global Images\. |
V | Controls if the Variable Name (Prompt) is visible in the User Interface. 1 = Visible, an 0 = Invisible. |
W - AB | Not currently used. |
AC - AI | Column AC contains info (Help Text) for the legacy Lookup tables contained in Columns AD-AI. Modern libraries now locate their tables inside the “LookUpTables” worksheet. |
D Workbook
The D! workbook represents the variables present in the Door Wizard, containing all the variables of the doors placed within your Microvellum database.
Column | Description |
A | Library Version & Tab Names |
B | Unique Global Category LinkID |
C | LinkIDCategory (Global Sub-Categories are Linked To Related Main Global Category Unique LinkID) |
D | Category Name |
E | CategoryLevel (1=Parent, 2=Child) |
F | Not In Use |
G | TabIndex # |
J | Unique Variable LinkID |
K | LinkIDCategory (Global Variables are Linked To Related Global Category Unique LinkID) |
L | Door Variable Name |
M | Door Variable Value |
N | Control Type |
O | Help Text |
P | Validation Code |
Q | Combo Box Text |
R | Quantity Value |
S | Prompt Color |
U | Picture |
V | Visibility (1=Visible, 2=Not Visible) |
H Workbook
Inside the Hardware workbook (H), the second worksheet named “HardwareLibrary” is where Microvellum populates the contents of hardware materials from the database.
The third worksheet named “MachineTokens” is where each machine token is stored for the corresponding hardware item. Tokens belong to hardware on the same row number on the “HardwareLibrary” worksheet. For example, the machine tokens for a hardware on row 15 in the “HardwareLibrary” worksheet would be found on row 15 of the “MachineTokens” worksheet. Tokens start on column A with the name followed by nine parameter columns. This repeats for each token.
Because hardware items and their respective tokens need to align “row to row”, it is important to never manually delete or insert hardware items in the spreadsheet. Or, if you do it will be required to repeat any row changes on the MachineTokens worksheet. An alternative workflow is to either use the Microvellum material interface or replace the name of a hardware item to say “deleted”. Doing this will tell Microvellum to delete the item, shift appropriate rows, and even remove it from the database.
For most hardware items a record of the item in the spreadsheet is not required.
Unless the hardware item is using associative machining (like a hinge), there is really no purpose for it to be in the spreadsheet. For example, drawer slides can often exceed several thousand rows and do not require associative machining. To avoid the H workbook from bloating up with thousands of unnecessary rows of hardware, which can contribute to slower processing performance, we can now tag specific hardware items with “Skip Spreadsheet Sync”. This will tell Microvellum to skip these products when doing its spreadsheet sync. This can provide a noticeable improvement in performance.
Hardware Library
A comprehensive list of all the different pieces of hardware within the Foundation Library. Hardware materials set to sync to spreadsheet will be generated in this sheet. Columns B, C, and F are considered carry-overs in the structure of the worksheet that contain variables which are not relevant to hardware, such as thickness or grain direction. As such, these columns can be safely left alone
Column | Description |
A | Hardware Name |
B | Unused thickness field; remains 0 |
C | Matches the hardware listed in Column A with a .dwg file located in the “Graphics” folder; largely unused, do not modify. |
D | Hardware Material Level (0= Library level, 1= Project level) |
E | LinkID Code |
F | Unused graining field; remains 0 |
G | Hardware Code |
H | Estimate Price of the Hardware Material (E = Each L = Per Linear Feet/Meter) |
I | Comments |
Machine Tokens
The Machine Tokens sheet in the H! workbook contains the settings of the various types of machining tokens of the hardware present in one’s library or project. The structure of the Machine Tokens workbook is parallel to that of the Hardware Library worksheet, with the tokens listed in the sheet’s rows being attached to the hardware in the corresponding row in the Hardware Library sheet.
The Machine Tokens sheet is color coded to separate the type/name of the hardware token from the token’s parameters. Dark blue columns (such as A, K, AE) contain the names of the machine tokens attached to the corresponding hardware, while the following 9 light blue columns all contain the default values of that token’s parameters.
For more information on the structure of machining tokens, consult this article.
To demonstrate, in the first row of this example Hardware Library sheet, the first hardware item listed is a 125° Full Overlay Knock-In Blum Hinge.
Moving to the Machine Tokens sheet, the first row on the sheet lists the specifics of a bore token, with the name in column A, and the values of the token parameters in columns B through J.
If one were to open the machining tokens of that specific piece of hardware, one would find the parameters listed according to what they reflect about the hardware, in terms of machining. The columns correspond chronologically with the parameters.
Column K (the next dark blue column) then starts the tracking of the next machining token associated with the hinge, and columns L-T then list the values of that second token’s parameters.
Blank columns mean that the hardware part listed has no more tokens or signify a blank or unused parameter.
Note that the columns listed below are a repeating pattern that repeats every 10 columns.
Column A: Machine Token Name/Type
Column B: Token Parameter 1
Column C: Token Parameter 2
Column D: Token Parameter 3
Column E: Token Parameter 4
Column F: Token Parameter 5
Column G: Token Parameter 6
Column H: Token Parameter 7
Column I: Token Parameter 8
Column J: Token Parameter 9
Prompts
The Prompts sheet of the H! workbook contains the variables and prompt options as they are defined in one’s Hardware Material Wizard. The wizard uses this sheet as a reference for organizing and calling on various different pieces of data during operation.
Column | Description |
A | Global Prompt Name |
B | Global Prompt Default Value |
C | Index Level (9= Standard, 6= Angled or Face Frame, 10= Drill, 5= Default Drawers, |
D | Help Text |
E | Library Version Number |
F | Prompt Category |
G | Unused |
H | Color value |
I - L | Unused |
M | Hardware Category (0= Hinges, 1=Mounting Plates, 2= Handles/Pulls, 3= Drawers, 4= Locks, 5= Additional Hardware, 6= Bathroom Partitions) |
N | Hardware Categories (used by Column M) |
O - Q | Unused |
R | Original Prompt Defaults Backup |
M Workbook
The M! workbook is comprised of the sheets that contain the data present in the Material File Wizard, containing the various sets of data and options for controlling the various materials within the software. This workbook contains 4 major sheets (plus the LookUpTables sheet), each representing one of the major material types within your Material File: Cut Parts, Sheet Stock, Solid Stock, and Buyout Materials. Edgebanding materials are located within the E! workbook rather than M!
Cut Parts Sheet
Material pointers are stored on the first worksheet, beginning on column A, and ending on column H. See below column references:
Column | Description |
A | Pointer name and defined on column B |
B | Pointer value. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the material name assigned to the pointers. |
C | Thickness of current pointer’s material. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the material thickness of current assigned material. Cell is automatically defined by Microvellum when added or edited. Defined name should be Pointer name with “_Thickness” added to end. |
E | Pointer GUID identifier. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the GUID identifier of current assigned material. |
G | Worksheet Identifier Value. “1” = sheet stock material | “2” = Solid Stock material | “3” = Buyout material |
H | Material Row Value. This is what sets the particular material for each pointer. This is generally set by Microvellum when the user is in the material UI. |
R | Material Group Name. This is what adds the pointer to a particular group. |
Sheet Stock Library
Inside the M! workbook, the second worksheet, named “SheetStockLibrary”, is where Microvellum populates the contents of sheet stock materials from the database. At certain times while using Microvellum, the program will get triggered to “sync” this list and the other 2 library sheets with what’s currently in the database. One could say these sheets are the “bridge” or “gateway” between the database and the spreadsheet. It is vital the two stay in sync, otherwise unpredictable results can occur.
See below list for details on what each column is for:
Column | Description |
A | Name |
B | Thickness |
C | MaterialEstimateSS_BO_EB |
D | ProjectLevel |
E | Link ID |
F | Grain |
G | Code |
H | Unused |
I | Comments |
J | Sheet Qty (legacy) |
K | Waste Factor |
L | Sheet Length (legacy) |
M | Estimate Price |
N | Alias Name |
O | Trim (legacy) |
P | Trim (legacy) |
Q | Trim (legacy) |
AX | Sheet Name |
AY | Sheet Qty |
AZ | Sheet Width |
BA | Sheet Length |
BB | Sheet.LeadingWidthTrim |
BC | Sheet.TrailingWidthTrim |
BD | Sheet.LeadingLengthTrim |
BE | Sheet.TrailingLengthTrim |
BF | Sheet.OptimizationPriority |
BG | Sheet.MaterialCost |
BH | Sheet.HandlingCode |
Solid Stock Library
The "SolidStockLibrary" sheet is where Microvellum populates the contents of solid stock materials from the database.
Column | Description |
A | Name |
B | Thickness |
C | Material Estimate Price |
D | ProjectLevel |
E | Link ID |
F | Grain |
G - I | Comments |
J | Sheet Qty (legacy) |
K | Waste Factor |
L - M | Unused |
N | Alias Name |
O - Q | Unused |
R | Labor Value |
Buyout Library
The "BuyOutLibrary" sheet is where Microvellum populates the contents of materials marked as buy out materials from the database.
Column | Description |
A | Name |
B | Thickness |
C | Material Estimate Price |
D | ProjectLevel |
E | Link ID |
F | Grain |
G - H | Unused |
I | Comments |
J | Markup |
K | Waste Factor |
L - M | Unused |
N | Alias Name |
O - Q | Unused |
R | Labor Value |
Prompts
The Prompts sheet of the M! workbook contains the variables and prompt options as they are defined in one’s Cut Parts Wizard. The wizard uses this sheet as a reference for organizing and calling on various data during operation.
Column | Description |
A | Alias names of materials
A1-A3: Unalterable basic prompts for measurement, width height and depth, those are product specific. |
B | Material Name |
C | Material Category (Custom Sheet Material = 6, Sheet Material = 3) |
D | The category/comment on the pointer and material |
E | Library Number/Version |
F | Lookup Table Materials |
G - L | Unused |
M | Variable Category (0= Default Construction, 1= Project Setup, 2= Material Options, 3= Title Block Info, 4= Render Finish Options) |
N | The Variable Categories. The options listed here are used by M to determine the category of a variable |
O - Q | Unused |
R | Backup copy of original Prompt Formulas |
E Workbook
The E! workbook is comprised of the sheets that contain the data of edgebanding materials. This workbook contains 2 major sheets (plus the LookUpTables sheet): EdgebandParts, EdgeBandLibrary, and Prompts.
Edgeband Parts
The EdgebandParts sheet is a reference/lookup list that other sheets' formulas point to, listing out the various edgebanding materials that are assigned to pointers in the material file.
Column | Description |
A | Pointer name and defined on column B |
B | Pointer value. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the material name assigned to the pointers. |
C | Thickness of current pointer’s material. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the material thickness of current assigned material. Cell is automatically defined by Microvellum when added or edited. Defined name should be Pointer name with “_Thickness” added to end. |
E | Pointer GUID identifier. Formulas in this column are automatically generated by Microvellum when new pointers are added. The formula should pull in the worksheet (column G) and row number (column H) to determine the GUID identifier of current assigned material. |
F | Material Row Value. This is what sets the material for each pointer. This is generally set by Microvellum when the user is in the material UI. |
R | Material Group Name. This is what adds the pointer to a particular group. |
Edgeband Library
The EdgeBandLibrary sheet is populated by the actual edgebanding materials available in the material file, listing out each material and its various different attributes.
Column | Description |
A | Name |
B | Thickness |
C | Material Estimate Price |
D | ProjectLevel |
E | Link ID |
F | Grain |
G - H | Unused |
I | Comments |
J | Markup |
K | Waste Factor |
L | Unused |
M | Part Size Adjustment |
N | Alias Name |
O - Q | Unused |
R | Labor Value |
Prompts
The Prompts sheet of the E! workbook contains the variables and prompt options as they are defined in one’s Edgebanding Material Wizard. The wizard uses this sheet as a reference for organizing and calling on various information during software use.
Column | Description |
A | Name of the Edgebanding Material
A1-A3: Unalterable basic prompts for measurement, width, height and depth, those are product specific. |
B | Material Name |
C | The value that dictates visibility in the Wizard interface |
D | Help text |
E | Library Number/Version |
F | Global Construction Options |
G - I | Unused |
J | Help Image in the Wizard interface |
K - L | Unused |
M | Variable Category (0= Default Construction, 1= Project Setup, 2= Material Options, 3= Title Block Info, 4= Render Finish Options) |
N | The Variable Categories. The options listed here are used by M to determine the category of a variable |
O - Q | Unused |
R | Backup copy of original Prompt Formulas |
W Workbook
The W! workbook is a connector between the Global workbook and the 3 library workbooks, M, H, and E (Materials, Hardware, and Edgebanding). The W! workbook is structured similarly to most other workbooks.
Column | Description |
A | Name of the Wizard Variable
A1-A3: Unalterable basic prompts for measurement, width, height and depth, those are product specific. |
B | Wizard Variable default value |
C | The value that dictates visibility in the Wizard interface |
D | Help text |
E | Library Number/Version |
F | Global Construction Options |
G - I | Unused |
J | Help Image in the Wizard interface |
K - L | Unused |
M | Variable Category (0= Default Construction, 1= Project Setup, 2= Material Options, 3= Formula Material Options, 4= Title Block Info, 5= Render Finish Options) |
N | The Variable Categories. The options listed here are used by W to determine the category of a variable. |
O - Q | Unused |
R | Backup copy of original Prompt Formulas |