The Valesco Excel Add-In was created to save you time when importing and exporting between Valesco and Excel.

 

The Valesco Export Add-In function is used to take a Bill of Quantities or Schedule of Works from Excel, find the bills, pages, items and descriptions, and format them into a specific file format Valesco can read. This format is known as a .VAL

Since the majority of bills don't share the same initial format, for this reason we created this guide, to show you how best to prepare your spreadsheet before import, which options best suit your bill, and what the resulting .VAL file actually represents.

 


 

 

Preparing the spreadsheet before running the Excel Add-in Valesco Export utility:

  • Ideally the worksheet should have these columns at the top of the page:
     Reference, Description, Quantity, Unit of Measure.
  • All Bill Items to have a Reference column
  • Where Bill Item References restart a sequence (e.g. A,B,C or 1,2,3)
     Valesco Export utility will create a new page for each new sequence
  • All Text Items to be clear of any References in the column above
  • All Descriptions to be in one column
  • Avoid use of (or remove) Carriage Returns from any Reference, Quantity or Unit fields
  • Any Bill Item Descriptions carried over to other cells must have a blank line separating them from the next unique description
  • Where Bill Item Descriptions are spread over a number of cells, the Bill Item Reference MUST be on same line as Bill Qty & Unit
  • Make sure there are no protected sheets, right click on the sheet tab at bottom, Protect sheet (only applies when you receive an excel format from a client)

 

 

Review of the Layout of BoQ or Schedule of Works received in Excel

 

Your file ideally needs to have these columns at the top of the page:

•            Reference

•            Description

•            Quantity

•            Unit

Any of these can be omitted when selecting columns, but Reference is essential for bill items, however for Schedules of Work, only the first two columns are usually available, as no quantities are generally provided.

The Reference column contains the item reference for each bill item. This is usually done in some sort of hierarchy (1.01, 1.02, 1.03, ... , 2.01, etc.), or in a simple list style (A, B, C, ...).

 

The Description column contains the text description of each bill item. There are few rules about this one - more or less any text will be copied to Valesco, but some of the less frequently used characters may not copy correctly.

 

It may be necessary on occasions to use the Excel CLEAN() command to remove any non-printable characters and extra spaces from within the cells.

 

Valesco Record Types

There are three types of records found within a bill page that can be created from importing the source Excel file:

•            Measurable items

These contain data about bill items, and require the Reference, Description, Quantity & Unit columns to be populated in order to form a complete record.

 

•            Descriptive records

These contain any text descriptions in the bill, and do not need a Reference, Quantity or Unit in their row. The majority of rows where only the description cell is filled will be imported as a descriptive record.

 

•            Page Titles

The Add-In uses item references to determine page structure. In short, the Add-In looks at the item reference and determines whether the numeric or alphabetic reference given is higher or lower than the one it just dealt with. If it detects a change of sequence, a new Page Title record will be created. The Add-In will attempt to pick up both Bill and Page titles from the file.

 

Excel Worksheet Layout

There are two indexes used in a standard bill of quantities - Bills and Pages. In a large project, Bills are used to represent subsections of work, and are formed by the Add-In using different Excel worksheets, so each sheet transfers to one bill. It is possible to put all worksheets into one bill if necessary, but a single worksheet to bill method is the default.

 

Pages are subsections of each bill, used to further categorise or split up items. Pages are created whenever an item reference repeats or resets within a bill, so pages can be placed one after another within a single worksheet.

 

There are two main formats of a received Excel document, Continuous and Delimited.

 

Continuous

 

Continuous formatting simply means that each item record in the bill takes up one row. References, Descriptions, Quantities and Units all occupy one cell (e.g. word wrapped) so that each new row equals a new record. In this layout pages can come immediately after one another in the worksheet - in the example below the first page has item references A through L, and then restarts references at A for the second page to start from line 18.


 

 

 

Delimited

Delimited formatting is used when item descriptions are a longer, and the author has not used word wrapping, so 2 or more consecutive cells are used to contain descriptions for one item. For this to work, the consecutive cells must be vertically adjacent - in the example below item 1.01 uses B4 and B5 for its description - and all records must have a blank line between them, so the Add-In can tell where one item starts and another ends.


References should be on the same row as the start of the description, and although it shouldn't matter which row the item Unit/Quantity inhabit, we recommend having these on the start row to limit errors.

 

 

Valesco Export – Processing Options

This page steps through the process of transforming a properly laid-out Excel file into a file ready to be imported to Valesco, and explains the options you are presented with.

Go to the ADD-INS toolbar in Excel, and click on the Valesco Export button.
 

Worksheet Selection

The first form that appears contains a list of the worksheets contained within the current workbook. On this screen you need to select the worksheets you wish to import to Valesco, either by selecting them individually or clicking the "Select All" button at the bottom of the form. As the text on the form says, Collection and Summary sheets should not be imported into the bill.

Once at least one sheet has been selected the "OK" button will become clickable, which is used to get to the next form.
 

Please note that any worksheets which have been password protected and any sheets that have been hidden in Excel will not appear in the selection box, since they cannot be accessed by the Add-In. If you wish to use a protected sheet you will have to temporarily remove the password for the length of this process.

Column Association

Once you have selected your sheets you will be presented with four drop-down boxes representing each of the columns discussed in the Layout section above. The text at the top of the form indicates which worksheet you are currently selecting for.

Select the relevant column for each, you will notice that each column turns the same colour as the interior of that drop-down box. Once you have selected a column it will be removed from the other boxes to prevent the same column being used twice.

 

 

 

It is also possible to select columns by using the Tab key to switch between boxes, and the Up/Down Arrow keys or the mouse wheel to scroll to your desired column.

If you need all the boxes to go back to blank so you can re-select, the fastest way is by clicking the "Reset" button above the Unit drop-down box.

Once you have selected your relevant columns, click "Commit". The same form will pop up again for every sheet you selected to import.

If you know all of your worksheets use the same columns then you can skip the repeating form by selecting the "Tag subsequent worksheets based on these selections" checkbox. The Add-In is smart enough to know that if one of your selected columns matches up to an empty one in a subsequent sheet, it will need to show this form again.

 

Formatting Options

The Formatting Options dialog contains a number of settings used to alter the final product, and are set the same for every worksheet you have selected. You may never need to use some of these settings since they make such specific changes, but they are there for your convenience should you require them.
 

 

Merge Multi-line Descriptions

This option is used to differentiate between Continuous and Delimited layout styles as discussed above.

 

The Merge Multi-Line Descriptions checkbox should be checked if you are importing a Delimited layout style, as this option will concatenate (merge together) item descriptions spread over several cells into one description and delete the empty rows. Attempting to use this option when your spreadsheet does not contain delimiting blank rows - or not using it when your descriptions are spread over two or more rows - will result in a Valesco bill that doesn't match your spreadsheet.

 

Convert Description Lines to Bill Items

This option decides the handling of items in the spreadsheet which have only a Reference and a Description (i.e. Quantity and Unit are blank). If this option is unchecked, these rows will lose their Reference and be converted to descriptive records. If it is checked, these rows will gain a standard Item Unit and Quantity of 1, and will be handled as measurable records, such as for Schedules of Work, where no quantities are provided.

 

Automatic Resourcing

Checking this box will automatically assign a resource from the existing master resource database to the item being exported if the item reference of the row matches the reference of the resources held in the resource database. Only use this option for Framework type contacts, where Schedules are uniquely referenced.

 

Add Comment

By default the Add-In will add an Excel comment to the A1 cell explaining what to do with your .VAL file to import into Valesco. Once you have used the Add-In a couple of times, this can be supressed by un-checking the box.

 

Bill and Page Numbering

The first option in this section - "Create a single bill from multiple sheets" - will concatenate all worksheets into one bill if ticked, instead of using one bill per sheet.

 

The grid below allows different options to determine how your bill and pages are numbered.

 

 

Once you have selected the right settings for your spreadsheet in the Formatting Options dialog, click "OK" and the Add-In will begin to work. For single sheets this will usually take a couple of seconds, but for larger, multi-sheet bills it could take up to a minute to process. A progress bar will show on-screen to let you know how things are going.