KBA-01797: Using Older Via Excel Templates to Import/Export Data

Question:

We recently upgraded our site to V2020 and want to use Excel templates to add Items to our Commitments and to maybe edit those Items in Excel also, as is now possible.  We used to add Items with Via Excel templates. Can we use those same templates now, or how do we convert those old templates in order to use them for this newer Item functionality?

Answer:

You might be able to use your existing Via Excel templates in V2020+, even if a few changes need to be made first in the template itself, in the Templates tool and in your rules. These instructions do not apply just to Commitment documents; they apply to Via Excel templates for other Doc types also.  However, the instructions below assume Microsoft Office 2016 or later.  (See Additional Comments for older versions of Office).

First, Investigate:

The Item Number is now the “key” for rows in the template, so Item numbers in the template need to match what is in the document or else new Items (that are not actually new) will be added.

  1. Go to the DocItemConfig rule group (in the Rule Maintenance tool on the System Admin Dashboard) and see if you have an ItemNumberFormat rule set up for your Doc type.  If you do not find one, the Doc type is using the default of D-3 (which means Doc number followed by three digits) unless it is one of the following Doc types.  A number alone in the Result Value means there is no Doc number prefix. (You can also look a your document to see how the Item numbers show up.)
  2. Note the formatting (in the Result Value) somewhere because you will need this information.

Changes to your template:

  1. Go to your Template library in the Manage Dashboard and download the template.
  2. Open your template and make sure that it has a ToSpitfire tab. (It is also possible that this tab was renamed according to the VXLConfig | ImportSheet rule, but ToSpitfire is the default.) If the template does not include an import sheet, additional modifications will need to be made: contact Support.
  3. Look for a To_RowFlag column. This column might be hidden (for example, Column A). If you find one, either delete the column or remove the defined name from that column.  The template cannott have a To_RowFlag column if it is going to use the Item Number column as described below. Contact Support if you need help unhiding and finding the column.
  4. Make sure that your template has a column for the Item number (such as Column B in our example below). If the template does not include this column, it is likely hidden. Contact Support if you need help unhiding and finding the column.
  5. Add one of the following formulas in the first cell in your Item Number row (B2 in our example).
    1. If your Items do not use the Doc number prefix AND always use consecutive numbering (because Items are never deleted), enter the following formula, where C2 is replaced by the first cell in your Description column:
      =IF(ISBLANK(C2),"",ROW()-1)
    2. If your document may end up with non-consecutive Item numbers, enter the following formula, where C2 is replaced by the first cell in your Description column:
      =IF(ISBLANK(@zRawItems_DocItem_DocItemNumber),IF(ISBLANK(C2),"","new"),
                   @zRawItems_DocItem_DocItemNumber)
      

      This formula will display the word “New” on new Item rows on the worksheet. Those Items will be numbered correctly when they are imported onto the document.

    3. If your Items do use a Doc number prefix (e.g., D-3 or D-4) and you want to display the Item number in the worksheet instead of the word “New”, use the following formula, where @zRawDoc_DocMasterDetail_DocNo is replaced by what the defined name refers to (see “Finding Defined Name References” below), C2 is replaced by the first cell in your Description column, and 000 is replaced by the number of digits after the D in your Item Number format:
      =CONCAT(@zRawDoc_DocMasterDetail_DocNo, 
           TEXT(IF(ISBLANK( @zRawItems_DocItem_DocItemNumber),IF(ISBLANK(C2),"",ROW()-1),
                @zRawItems_DocItem_DocItemNumber),"000"))


  6. Once you have the correct formula in row 2, drag and copy that formula down in your Item Number column for as many rows as you think you’ll need in your Spitfire document.
  7. Select the column and format the cells in that column with the appropriate formatting to match the formatting of the Item number. Since you want leading zeros, use a Custom formatting with the correct number of digits. For example, use 0000 to indicate 4 digits.
  8. Save and close Excel.

Changes in the Templates Tool:

  1. Once you have modified the template, upload it to your Template library.  This template will be replacing the older template.
  2. Change the Template Type from Via XL for Doc Items to Attachment, Attachment (Auto) or Attachment (all).  [For an explanation of the differences, see Attachment Type Templates.]  The Data from Doc option should be checked.
  3. Change the Description if appropriate then save your changes.

Changes in Rules Maintenance:

  1. Go the the VXLConfig rule group and expand it.
  2. If the Enabled rule is not there for your Doc type, add the rule with the checkmark. In the example below, import of Items is ON for Bid Packages and Commitments, but OFF for Budgets.
  3. If you would like to be able to “round-trip” your data (i.e., data that you enter in Excel shows up in your Items tab and data that you enter/change in the Items tab shows up in your Excel file, back and forth) also add the following ExportEnabled rule:
  4. You might also want to read about the ImportAutoLoad and ImportRecalc fields in KBA-01606 to see if you want to change their defaults.
  5. Save your changes.  Your template is ready to use!

To use the template:

  1. Do NOT select the old Via Excel option.
  2. Either attach the template file to the document through the icon or find the template file already attached on your document.
  3. Open the Excel file and enter your data.  When you save and close Excel, the data will get copied to the Items tab on your document.

Finding Defined Name References

If you are using a version of Microsoft Office prior to 2016, all @DefinedNames used in our formulas above will need to be replaced by what they refer to, starting at row 2.  In addition, if your Item numbers are configured to use a Doc number prefix (such as D-4), you’ll need to replace @zRawDoc_DocMasterDetail_DocNo in your formula with the reference (as shown in the picture for option C in the “Changes to Your Template” instructions above).

To find a Defined Name reference:

  1. While in your template in Microsoft Excel, select the Name Manager option on the Formulas ribbon.
  2. Scroll to find the @DefinedName mentioned in the formula, for example @zRawDoc_DocMasterDetail_DocNo.
  3. Copy the Refers To to your formula making the following changes:
    1. Do not include the = sign
    2. Remove the colon (:) and everything after it
    3. Add $2 if the the Defined name begins with zRawDoc and just 2 if the Defined name begins with zRawItems
    4. For example,
      =CONCAT(@zRawDoc_DoczMasterDetail_DocNo,
      becomes
      =CONCAT(RawDocument!$F$2,

 


KBA-01797; Last updated: July 24, 2020 at 10:40 am