Interfacing EstimatorVJ and Excel

Generally

EstimatorVJ will integrate directly with current Excel Spreadsheets OR create new Excel Spreadsheets as the case may be.

The following illustrates how Excel and EstimatorVJ interface together to minimise the effort required by Clients, Engineers, Quantity Surveyors, and Estimators in the Project Tendering process. 

In words, what this illustrates is:

• The Client and/or Engineers use EstimatorVJ to

  • Create the client's schedule as an Excel spreadsheet

  • Measure the quantities for each item (if required) using on-screen measurement from drawings optional

  • Generate a Client Schedule or Bill of Quantities in Excel format using the Print option

  • Submit this Schedule to the Tenderers

• The Tenderer's Estimators use EstimatorVJ to

  • Import the Client's schedule from the Excel spreadsheet

  • Price all items required

  • Incorporate all markups, overheads, etc

  • Insert the Bid values back into the original Excel Client Schedule through the Print option

  • Resubmit the original Client Excel Schedule completed

Creating the 'Schedule of Items' or 'Bill of Quantities' in Excel Format using EstimatorVJ

Here is the procedure within EstimatorVJ to create your Schedule or Bill:

• Create the Project

  • Use a Template with the appropriate format
    CCSi will be creating a series of various formats over the next few weeks which will be available for downloading within VJ from our website

• Enter the appropriate items and measure the quantities as applicable

  • Quantities can be measured directly off on-screen drawings into EstimatorVJ
    If you have multiple monitors attached, you can choose which one to view the drawings on

• When all items have been entered and/or quantitified, use the Print option to create the Excel format Schedule or Bill of Quantities
  

  • Click the 'Print' button in the Projects TAB
  • Select the appropriate report - normally the 'Traditional Bills of Quantities' option
  • Select which Sections you want included in the Schedule
  • Select the option 'Print to XLS'
    
    

    

  • Click the button 'File Name' and a File Dialog will open ready for you to name the Excel file
    By default, this File dialog will initialize the 'save' folder as '<current project folder>/Documents'
  • Give the file a name (e.g. MySchedule) - the program will automatically add a '.xls' extension to it for completeness
  • Click either the 'Preview' or 'Print' buttons
  • VJ will now create the Excel file as well as the previewed/printed report

Using EstimatorVJ to Price the Client Schedule then transfer the Estimated Item Costs back into the Client's Excel Schedule of Items

Here is the procedure to use EstimatorVJ to price and then 'fill in' the Bid values for each item back into the Client Schedule (Schedule) or Bill of Quantities (BoQ) - NOTE Your actions will be shown in RED.

• Create the Project
  

  • Use a Template with the appropriate format to match that of the Schedule/BoQ
    CCSi will be creating a series of various formats over the next few weeks which will be available for downloading within VJ from our website
  • Do not enter any data yet - this will be discussed later
• Add the Excel Spreadsheet containing the Schedule/BoQ to the Project's Documents
  • • ALT+click on the  button 
    • Choose the Workbook from the File Navigator
      The Workbook will now be COPIED into the Project's Document folder. This makes it very easy to access (just click on the button and choose from the list), as well as this Workbook is now integrated into this Project. Furthermore it is also saved in any Project Backups subsequently done.
      

• Generate a KEY column
  A KEY column is a column in both the Excel Workbook and the Project Estimate containing a series of unique characters in both which is used to identify the same item in both the Workbook and Estimate. See below for an example - the KEY column here is the 'ItemRef' column:
  
  In the Workbook:
  
  
  In the Estimate (after the schedule has been imported):
  
  
  
  

• Create the KEY between the Workbook and EstimatorVJ
  The following steps will insert a column in each of the Workbook sheets with a unique reference number in it for each row. This is the 'link' between the items that will be priced in the Estimate with the items in the Workbook enabling the 'filling-in' process to occur properly.
• click the 'XLS Ref' button in the 'System' TAB
• Select the Workbook you added to the Project in the previous step
• EstimatorVJ will now open the Workbook and add the KEY's
• Confirm this has been done
• Click the  button
• 'Open' the workbook
  You should now see the first column has been added, and there is a unique number on each line of each sheet similar to the example above
• Copy the items to be priced from the Workbook into the Project
• Prepare the Project
• Edit the Section Names
• Add an 'ItemRef' column as the KEY column
• Click anywhere in the 'CO' column
• Right mouse click in the 'CO' column  and choose 'Add Column' 
  The program will duplicate the CO column and call it 'Copy of...'
• Rename this Copy
• Right mouse click on the heading 'Copy of....' and choose 'Rename Column'
• Type in 'ItemRef' as the new Column name and click OK
  
• Move 'ItemRef' to the first column position
• Click on the 'ItemRef' heading and while holding down the mouse button drag to the left until it positions itself
• Import the Section Names from the Workbook
• Switch to the Workbook we opened earlier
• Range the data we want to copy into EstimatorVJ and press 'CTRL+C'
• Switch back to our Estimate Names worksheet in EstimatorVJ
• Click on the first cell where we want to paste the information and press 'CTRL+ALT+V'
  We add the 'ALT' key in the keystroke because we are pasting from the SYSTEM clipboard and not the EstimatorVJ clipboard
  This completes importing the Section names
• Press 'ESC' to close the Section Names editing process
• Add an 'ItemRef' column as the KEY column to the default Estimate Worksheet
• Open the FIRST Estimate Worksheet
• Click anywhere in the 'BQREF' column
• Right mouse click in the 'BQREF' column  and choose 'Add Column' 
  The program will duplicate the BQREF column and call it 'Copy of...'
• Rename this Copy
• Right mouse click on the heading 'Copy of....' and choose 'Rename Column'
• Type in 'ItemRef' as the new Column name and click OK
  
• Move 'ItemRef' to the first column position
• Click on the 'ItemRef' heading and while holding down the moue button drag to the left until it positions itself
• Make sure 'ItemRef' is set as printable
  This makes sure that later on when the Report is Printed, it contains this KEY column
• Right mouse click on the 'ItemRef' heading then cursor down to 'Options', and make sure that there is a tick next to the 'Printable' option
  
• Make this Estimate Worksheet the default
  This makes every new Estimate Worksheet we create the same as this
• Click the option 'Set as Default' in the 'Defaults' TAB
  The program confirms that the settings have been saved.
• Copy the relevant data from each Workbook sheet into the equivalent Section in the Project, making sure that the data is copied into the correct Project Section columns
• Price the Estimate in EstimatorVJ
• When the pricing is complete, 'fill-in' the Client Workbook with the prices derived in EstimatorVJ
• Click the Project->Print' button
• Click the  'Update XLS' button
• Click the 'File Name' button and choose the Workbook from the list
• Complete the following dialog box:
  
  
  
  
  
  
• If this relationship is a common one, then click the 'Save' button
  Specify a meaningful filename to save these relationships. They can then be Load(ed) next time this update has to be done
                      
  
  Two possible dialogs will appear; one for the Project Summary, and one for the Project Sections; which one it is can be seen from the title in the dilaog title.
  The first set of text boxes need to be filled in with the Column identifier in the Workbook that corresponds with the column represented by the text box; e.g. 'CO', 'SECTION NAME', etc.
  Below is an example Workbook that we will be 'filling in'.
  
  
  
  In this scenario we are only interested in 'filling in' the COST column in the Summary Sheet of the Workbook, so all we do is place the column identifier 'F' in the COST text box. If we wanted also to 'fill in'  say the QTY and RATE columns, then we would place the column identifiers 'D' and 'E' in the QTY and RATE respectively in text boxes as well. Any text boxes left blank, will be left untouched
  
  Finally, we have to deal a bit differently with the last two text boxes. 
• 'Which is the Key Column in the Workbook'
  This will normally be the first column 'A' - remember in a previous step we created this KEY column by clicking the 'System->XLS Ref' button, so enter this column reference
• 'Which is the KEY column in the report'
  This is the column heading in the report which contains the KEY; in this case the 'ITEMREF' column, so enter 'ITEMREF' in the appropriate text box
  
  This is how the dialog should look when filled out:
  
  
  
• Click 'OK' to complete the operation
• You can now view the result of this operation by clicking the button, selecting the Workbook, then clicking 'Open' 

• Click the 'Print' button in the Projects TAB
• Select the appropriate report abd options
• Select the option 'Print to XLS'
  
  

  

• Click the button 'File Name' and a File Dialog will open ready for you to name the Excel file
  By default, this File dialog will initialize the 'save' folder as '<current project folder>/Documents'
• Give the file a name (e.g. MySchedule) - the program will automatically add a '.xls' extension to it for completeness
• Click either the 'Preview' or 'Print' buttons
• VJ will now create the Excel Workbook as well as the previewed/printed report
• If you have created the Workbook in the '<current project folder>/Documents' folder (default), then you can view the result quite easily by clicking the   button, selecting the Workbook, and clicking the 'Open' button.

• Open any Worksheet and SELECT the data you want to Export
• Click the 'Worksheet->Export' button while holding down the ALT key
• A File Dialog will open ready for you to name the Excel file to receive the exported data
  By default, this File dialog will initialize the 'save' folder as '<current project folder>/Documents'
• Give the file a name (e.g. MySchedule) - the program will automatically add a '.xls' extension to it for completeness
• Click either the 'Save' button 
• VJ will now create the Excel Workbook
• If you have created the Workbook in the '<current project folder>/Documents' folder (default), then you can view the result quite easily by clicking the   button, selecting the Workbook, and clicking the 'Open' button.