Question:
How do I add or edit Items using an Excel Workbook attached to the document?
Answer:
This answer requires V2020 of sfPMS. Earlier versions had significant limitations: ask support about upgrading.
You can work with document Items using attached Microsoft Excel workbooks. Setting up the Excel Workbook does require following certain guidelines and some rule setup is required. Ask your implementer for help. See KBA: 01606 and KBA-01797 – Using Older VXL Templates.
Differences between older Via Excel option and this new functionality
In V2019 and earlier Via Excel meant finding the correct option from the Item’s drop-down menu, selecting the right choice from a (possibly confusing) dialog box, and having the document closed while you worked on the Excel file.
Legacy Via Excel in V2019 and Earlier
The new way of working with items Via Excel involves selecting the Create from Attachment icon on the Attachments tab while your document remains open—a process that is very familiar.
When the user opens the workbook, if “bookmarks” are enable, the export data is refreshed from the current data on the Items tab. When the user closes the workbook and the changes are uploaded, Spitfire usually detects the changes and automatically imports from the cells mapped by the defined “To_” names. You can also trigger the import manual from the document item grid menu (Import from Excel option)–this helps if you edited an offline file hours ago and just imported it.
Template Overview
Spitfire pushes (exports) data into three (typically hidden) worksheets: RawDocument, RawAddr and RawItems. Excel Defined Names are established for each field or column.
Your users will generally work on a sheet named ToSpitfire, although you can change this default using VXLConfig | ImportSheet. Regardless of the sheet name, the import locations are enumerated using defined names in the pattern
To_alias_field
(for example, To_DocItem_Description, To_DocItemTask_Quantity, To_SPRLineDetails_TotalPercentRequest).
Note that the export and import cells do not overlap! During the export phase, spitfire will mirror the item data to both the RawItems_ location and the To_ location. You can also use all sorts of Excel features, formulas and macros.
Getting Started
Simple worksheets will define the “To_” name to be an entire column. You can also define a target range, which allows for fancy header rows on your ToSpitfire worksheet.
-
- You must include a To_DocItem_DocItemNumber column. Information in this column will be used to either match an existing Item or add a new Item with the specified Item Number. If empty, the row is ignored.
- To_RowFlag may still work as before, but we recommend using To_DocItem_DocItemNumber.
- You can provision rows with formulas as needed. You might have an ‘inventory’ or lines available, or just a work area for a maximum number of rows. If your “To_” defined names are columns, formulas in row 2 are copied down. If your “To_” defined names are ranges, formulas are copied from the row above the range.
- Add the template to the template library as an Attachment template. If your existing Via Excel templates are good to go, you can just change their Template type from Via Excel to Attachment. Make sure the template:
- Does not include values specific to a document; there can be plenty of static information and calculations.
- Has a “ToSpitfire” sheet in the workbook.
Other Defined Names
- To_ColumnLimit – rarely used, limits the clear area to columns left of this column.
- To_DoNotClearRows – disables clearing rows on this To_ worksheet. Recommended for “picker” style import sheets
- To_RowLimit – only necessary if your “To_ worksheet has footer rows, stops the clear. Typically, place the To_RowLimit defined name to be your first footer row.
What are the valid To_ Defined Names?
See your RawItems sheet and the Excel Name Manager (Formulas tab). Every zRawItems_ defined name can be used on your ToSpitfire sheet.
Security
Anything the Excel workbook allows into the To_ defined cells will be imported by sfPMS. Your template therefore becomes responsible for both SOP and “security”.
- Consider using Excel protection features to limit editable cell ranges. Possible example: retention percent.
- Avoid using To_ defined names on data that should (for security or SOP) be export only. Use Excel formulas to mine data from zRawItems_ for such read only data.
- Remember to use CODES on columns that require them (for example: To_DocItem_Shop, etc).
Testing
- Open a document.
- Go to the Attachment tab and select the Create from Attachment icon (as shown above).
- Select the correct Via Excel template.
- When the Excel file opens, enter Item data on the rows. You can type directly into the worksheet or copy data from another Excel workbook.
IMPORTANT: You must supply Item numbers in the To_DocItem_DocItemNumber column. You might use a formula based on the sheet row number. KBA:01606 has several examples. - Save and close the Excel workbook.
- Data from the workbook will be copied to the Items tab.
- If you reopen the now attached Via Excel workbook you will see your Item data.
- If you make changes to your Via Excel workbook those changes will be uploaded to the Items tab, overriding what is there.
Additional Comments:
See separate documentation on editing Change Orders Change Item Budgets using Excel.
KBA-01629; Last updated: October 5, 2020 at 9:58 am