Home

Linking

1. estimates Linked folders and tasks can also be copied from one estimate to another enabling you to quickly reuse work from a previous similar estimate See Also Commands For Linking at the end of this chapter for additional linking options Linking Estimates to Excel Workbooks CostLink AE includes the ability to link to data in Excel 2000 version 9 0 and higher 104 BSD CostLink AE User Manual Chapter 7 Linking NOTE You must have version 9 0 Excel 2000 or higher of Microsoft Excel in order to use the linking feature Linking to Excel is not available using Windows 7 or later Using this ability you can maintain models within Excel spreadsheets that require only parameter changes in Excel to fill the quantity fields in a CostLink AE project Both folder and task quantities in CostLink AE can be linked to cells in an Excel workbook You can create a CostLink AE project that you will use as a template to create new projects The template would have links to an Excel model template that you maintain in parallel with the CostLink AE template You can make copies of the Excel model template to link to projects made on the CostLink AE template When you edit the parameters in the model spreadsheet the changes are made in the linked project When you are finished editing you can suspend the links You can link a single cell in Excel to multiple items in an AE project as well as link a single worksheet to multiple AE projects
2. workbook the links will update if you have the automatic update turned on or you can choose the Refresh All Links from Sources command to manually update the links This command is a toggle By default it is turned off links will update automatically as the link sources are edited When the command is selected a checkmark appears next to it in the menu indicating that it is on links will not update automatically as the link sources are edited To turn it off again choose the command again You may choose to turn this command on while you are editing so that you do not have to wait for links to update Use this command if you no longer want the estimate linked If you choose this command a message will open asking you to confirm that you want to remove the links You can choose Cancel if you have changed your mind When you click OK the links will be removed and a message will open stating that the links have been removed The quantity fields of the previously linked items will be white in the background indicating that you can edit the quantity and the note indicators and the green Excel link indicators will be removed from the icons of the folders and or tasks The values that they were last linked to will still be in the Quantity fields but they will not update if the link sources are edited BSD CostLink AE User Manual 115 Chapter 7 Linking 116 BSD CostLink AE User Manual
3. before you can link to it To give it a file name save the Excel workbook Your links are saved in a database called aecLMDat mdb This file will probably be located in your C CostLink AE System folder if you installed CostLink AE in the default location However if you have installed BSD SpecLink BSD PerSpective or BSD CostLink CM aecLMDat mdb files are also located in the System folders of those programs and your links may be stored in one of those aecLMDat mdb files instead Do not delete the aecLMDat mdb file unless you want to delete all of your links You can link one CostLink AE estimate to cells on as many different worksheets in as many different workbooks as you need to Do not use duplicate range names within the same worksheet in Excel It is faster if Auto Recalculate Markups is turned off in CostLink AE while you are making and refreshing links Toggle Auto Recalculate Markups on and off by choosing it from the Tools menu When it is on the command has a checkmark next to it When editing an Excel workbook that is already linked keep the following in mind Once you have pasted links into CostLink AE do not change the sheet name in Excel Excel workbooks that are linked to CostLink AE projects are automatically saved as you edit them Commands For Linking Linking in CostLink AE is created maintained edited and removed using the following commands in the LinkMan menu BSD CostLink AE User Manual 113 Cha
4. 5 Click OK 6 Switch to CostLink AE using one of the following methods Windows Taskbar Click the CostLink AE button in the gray bar across the bottom of the screen that lists all the programs tasks that are currently running or Alt Tab On your keyboard hold down the Alt key while you press and release the tab key Continue pressing and releasing tab until the CostLink AE icon is the selected program and then release the Alt key or click on the CostLink AE window Size your Excel and CostLink AE windows so that you can see both and use your mouse to click on the window you want 7 In CostLink AE locate the folder or task that you want to link Click on its parent in the tree panel so that the folder or task that you want to link is listed in the sheet panel 8 Click in the quantity field for the folder or task in the sheet panel 9 Choose LinkMan gt Paste Link from Source The quantity field displays the current value in Excel If you update the Excel workbook the quantity field will update to the new value BSD CostLink AE User Manual Chapter 7 Linking Tips For Linking When creating links from CostLink AE keep the following in mind You must have opened CostLink AE at one time on your computer before you can start linking to Excel You do not need to keep CostLink AE open while you are working on the spreadsheet you just need to have opened it at some point The Excel workbook needs to have a file name
5. Chapter 7 Linking This chapter contains information and directions for linking quantities on folders and tasks to the quantities of higher level folders or to an Excel workbook so that you can easily change the cost of the estimate based on the size of the project See the following topics e Linking to parent in CostLink AE e About linking to Excel e Setting up Excel for linking e About parameter workbooks e About aecLinkMan e Excel features used in linking e Directions for linking to Excel e CostLink AE commands for linking BSD CostLink AE User Manual 101 Chapter 7 Linking Quantity Options 102 CostLink AE has three options for entering the quantity on folders and tasks plus a fourth method that is used when the estimate is based on a model C Use Quantity Entered C Use MS Excel Value Times C Use Parent Quantity Times 102 Use Formula Value Times Oty amp UOM joo x Use Quantity Entered C Use MS Excel Value times C Use Parent Quantity times Quantity options for folder Quantity options for tasks shown for models template e Use Quantity Entered This is the default except for task quantities established by a cost model Select Use Quantity Entered to enter the quantity for this task in the Item Form or in the Sheet Panel e Use MS Excel Value Times This option is selected automatically when you link your quantities to an Excel spreadsheet You can multiply the v
6. CostLink AE identifies the Excel cell by the name and location of the workbook the name of the worksheet in the workbook and the range name that you entered for the cell e Comments Comments in Excel have some similarity to notes in CostLink AE Comments can be inserted into any cell by clicking on the cell and choosing Insert gt Comment Cells that contain comments are identified by a red triangle in the upper right corner of the cell Excel cell with a comment Read the comment by holding your mouse over the cell You can edit the comment by right clicking on the cell and choosing Edit Comment from the context menu Editing the comment includes resizing the comment so that you can read the entire comment and also editing the text in the comment You can remove the comment by right clicking on the cell and choosing Delete Comment When you copy a cell to link to CostLink AE a comment is automatically inserted identifying that the cell is linked BSD CostLink AE User Manual 111 112 Chapter 7 Linking Directions for Linking to Excel In general the following steps are performed to link an Excel workbook to a CostLink AE estimate 1 Make sure the workbook is saved 2 In Excel click on the cell that you want to link 3 In Excel choose Edit gt Copy as CostLink Source The Enter Range Name dialog box will open 4 Enter a name for the cell The range name can be as descriptive as you like but cannot have any spaces
7. When you change a cell in a linked worksheet and that change updates the values of other cells the corresponding values in AE change These changes will be made and saved even if the CostLink AE project is not open When the project is opened later you can choose whether or not to update the values This feature allows you to take advantage of model spreadsheets In a model spreadsheet you enter the overall size of a job and then list some or all the folders and tasks that are needed in an estimate for that type of job For the folders and tasks enter formulas that calculate their quantities based on the overall size of the job In CostLink AE you create all the folders and drag in all the tasks for your estimate However instead of typing in the quantities for the folders and tasks you can paste links from the corresponding values in Excel One Time Set Up In Excel Note that Linking to Excel is not available using Windows 7 or later In order to link a CositLink AE estimate to Excel you need to add a COM Add In to your Excel installation NOTE COM stands for Component Object Model Microsoft Excel s Help defines a COM Add In as a supplemental program that extends the capabilities of a Microsoft Office program by adding custom commands and specialized features The particular COM Add In that must be added was created by BSD to add a command to the Excel Edit menu Copy As CostLink Source that enables linking You will only nee
8. able OK v Links for MS Excel Remove E Location C WINNT system32 BSDOFcAI dll Load Behavior Load at Startup COM Add Ins dialog box listing BSD Links for MS Excel 6 Click OK Excel is now set up for linking About Parameter Workbooks in Excel Parameter workbooks used in linking will usually have the following characteristics e Nocosts will be entered in the parameter workbook The costs will come from the assemblies and line items in CostLink AE The sole purpose of the parameter workbook is to calculate the quantities of some or all of the items in the estimate based on the overall size of the job e The parameter workbook can span multiple worksheets and even multiple workbooks e f you rename the worksheets in your workbooks from Sheet1 Sheet2 etc to something more descriptive do not use any spaces hyphens or underscores in the sheet names CostLink AE cannot link to a cell on a worksheet that has a space in its name NOTE Do not rename a worksheet if links have already been copied from it e Parameter workbooks can be created by exporting a CostLink AE report of the estimate to Excel format This way you will get a list of all the folders and tasks in your project in Excel You would then add information for the overall size of the job and enter a formula for each task that will calculate its quantity based on the overall size of the job See Chapter 5 for information on exporting BSD CostLi
9. alue from the Excel spreadsheet by a number or decimal See Linking Estimates to Excel Workbooks in this chapter for directions e Use Parent Quantity Times Use this option to calculate the quantity for this item based on the quantity of the parent folder You can multiply the value of the quantity of the parent quantity by a number or decimal See Linking to the Parent Quantity in this chapter for directions e Use Formula Value Times This is the default for task quantities when a model is being used as the basis for the cost estimate You cannot select this option and when you choose one of the other quantity options for a particular task the task is permanently disconnected from the model and cannot be reconnected When Use MS Excel Value Times Use Parent Quantity Times or Use Formula Value Times is selected the task or folder icon will have a green square Excel symbol in the upper right corner When the Use MS Excel Quantity times option is selected the icon will also have a cyan note indicator in the bottom left since the link to Excel automatically adds a note as described later in this chapter The icons are as follows T a and E When linked to Excel the Parent Quantity or a model the quantity field in the Sheet Panel and in the Item Form will be grayed out BSD CostLink AE User Manual Chapter 7 Linking Linking to the Parent Quantity CositLink AE allows the quantities of folders and tasks to be linked to the qua
10. d to add this once for each computer using the Excel linking feature You need to do the following two tasks Directions follow BSD CostLink AE User Manual 105 Chapter 7 Linking Add the COM Add Ins command to your Excel Tools menu so that you can install the COM Add In Install the COM Add In Adding the COM Add Ins Command To the Excel Tools menu 1 Open Excel 2000 version 9 0 or higher 2 Click on the Tools menu 3 Show all the commands in the Tools menu by clicking the arrows at the bottom of the Tools menu 4 If you see COM Add Ins in the Tools menu skip to Installing the COM Add In in this chapter NOTE The command will be listed as COM Add Ins not Add Ins In a standard installation of Excel COM Add Ins is not included on the Tools menu 5 If you do not see COM Add Ins in the Tools menu click Customize in the Tools menu The Customize dialog box opens to the Toolbars tab or to the last tab that was viewed in the Customize dialog box 6 Click the Commands tab The Commands tab opens 106 BSD CostLink AE User Manual Chapter 7 Linking Toolbars Commands Options Categories Commands Window and Help Drawing AutoShapes x Save as gt Selected command Description Modify Selection The Customize dialog box Commands tab 7 Inthe Categories list click Tools The Commands list updates to show all the commands in the Tools category 8 Scroll through
11. n the Quantity field but it will not update if the workbook is edited Refresh All If you have chosen to Disable Updating from Sources either by Links From choosing the command from the LinkMan menu or by choosing to Sources not update links when you open an estimate that is linked to Excel choose this command to refresh the links without turning the automatic updating back on 114 BSD CostLink AE User Manual Change Excel Links Source Disable Updating from Sources Remove All Links from Sources Chapter 7 Linking This command applies to linking to Excel only Use this command to link the project to a different Excel workbook The best way to do this is to first create a copy of the workbook that the project is currently linked to This way the information will be located in the same places in the new workbook When you choose this command a Select New Excel Links Source dialog box will open Navigate to click on and open the workbook you want to link to A message will open asking you to confirm that you actually want to change the links source You can click Cancel if you have changed your mind When you click OK if you have the new Excel workbook open it will automatically update the links and open a message to confirm that the links were updated If you do not have the new Excel workbook open a message will open stating that you need to open the Excel workbook in order to update the links Once you open the new Excel
12. nk AE User Manual Chapter 7 Linking About aecLinkMan Each time you open CostLink AE or an Excel workbook that is linked to a CostLink AE estimate a program called aecLinkMan launches in the background While you are running CositLink AE or an Excel workbook that is linked to a CostLink AE estimate you can press CTRL ALT Delete to open your Task Manager and you will see aecLinkMan listed This is the program that enables linking between CosiLink AE and Excel Since this program is running in the background you do not need to have both AE and Excel running in order to edit a linked workbook or a linked estimate If you edit a linked workbook the next time you open the estimate that the workbook is linked to aecLinkMan will prompt you that the workbook has been edited and ask if you want to update the links Excel Features Used in Linking Linking automatically incorporates the following Excel features e Range names In Excel you can name a cell or a group of cells called a range and then use the range name in formulas instead of remembering or looking up the cell reference B2 D4 etc e Range names cannot include spaces or punctuation and they cannot begin with a number Instead of spaces you can use underbars _ You can use a number anywhere within a range name except as the first character e When copying a cell to link to CostLink AE you will be required to enter a range name When you paste the link into the estimate
13. ntities of the folders that contain them The folder that contains a folder or task is considered the parent and the subfolder or task is the child On the subfolder or task enter a value by which to multiply the quantity of the parent folder in order to calculate the quantity of the child When you change the quantity of the parent the quantity of the child will be changed also Example In the Fire Station Sample Project the Standard Foundations folder contains a Strip Footings folder Standard Foundations is the parent and Strip Footings is the child The indentation of Strip Footings under Standard Foundations represents the parent child relationship Standard Foundations 18 155 00 SF EB Strip Footings 54465 LF Standard Foundations and Strip Footings as example of parent and child folders The Standard Foundations folder has a quantity of 18 155 square feet SF Based on similar projects there will be about 30 linear feet LF of strip footings for every 1 000 SF of standard foundations Instead of calculating and entering the quantity of strip footings the Strip Footings folder quantity can be linked to the parent folder times 0 03 To do this open the folder item form for Strip Footings either by double clicking on it or by right clicking and choosing Edit Folder Then select the option Use Parent Quantity times The field to the right of that option turns white and has a default value of 1 Enter 03 in the field The Quantity i
14. pter 7 Linking Paste Link After using the Copy as CostLink Source command in the Edit menu from Source in Excel and giving the cell a Range Name in the sheet panel of your estimate in CostLink AE click in the quantity field of the folder or task that you want to link and choose this command The following things will happen The field will display the value of the cell in Excel The background of the quantity field will turn gray indicating that it cannot be edited Anote will be added to the task or folder detailing which cell the quantity is linked to Agreen Excel icon will appear in the upper right of the task icon to indicate that the task or folder is linked When the item form is opened for the linked task or folder the Use MS Excel Value times option will be selected Delete Link If you no longer want a particular quantity linked click on the folder or from Source task in the sheet panel and choose this command A message will open asking if you are sure you want to delete the link If you do not want to delete the link choose Cancel Click OK to delete the link Once you click on a different folder in the tree panel and back on the parent of the item you deleted the link to the quantity field will be white in the background indicating that you can edit the quantity and the note indicator and the Excel link indicator will be removed from the icon for the folder or task The value that it was last linked to will still be i
15. s calculated and shown in the Quantity field BSD CostLink AE User Manual 103 Chapter 7 Linking Folders for Fire Station Sample Project xi General r Folder Definition Tag Oo Title e Footings Qty amp UOM 544 65 LF 7 C Use Quantity Entered c Use MS Excel Value times 003 Use Parent Quantity times m Cost Summary Unit Cost Base Cost 28 07 15 288 Cost To Prime 25 94 14 127 Cost To Owner 32 95 17 948 Project Cost 36 85 20 071 Insert T Lock Back Next Close Strip Footings folder set to Use Parent Quantity times 0 03 Now if the quantity of Standard Foundations needed for this estimate changes the linear feet of strip footings will change accordingly You would also link the quantities of the tasks under the strip footings folder to their parent Strip Footings so that their quantities would change changing the cost of the estimate This is useful over the life cycle of a single estimate Early in the design the plans may be for a certain size building As the plans become more definite the size may change many times Instead of recalculating and re entering every quantity if you have linked the tasks and subfolders to their parents you may only need to change the quantities of a few folders at the top of the estimate This is also useful in the rapid preparation of budgets what if analyses and progress
16. ted release the mouse button COM Add Ins is added to the Tools menu 13 Close the Customize dialog box by clicking the Close button Installing the COM Add In For Linking To Excel 1 In Excel choose Tools gt COM Add Ins BSD CostLink AE User Manual Chapter 7 Linking The COM Add Ins dialog box opens Add Ins available Add Remove i Location Load Behavior The COM Add Ins dialog box 2 Click the Add button The Add Add In dialog box opens Add Add In kin dd 3 Click the Look In dropdown cl Addins and click on the drive that Windows is installed on This is usually the C drive 4 Double click on the folders listed below for the version of Windows that you are running NOTE If you do not know which version of Windows you are running open Windows Explorer you can do this either by holding down the Windows key on your keyboard while you type the letter E or by right clicking on the Start menu and choosing any of the Explore options and choose Help gt About Windows for Windows Vista or XP double click on the Windows folder and then double click on System32 for Windows 2000 double click on the WINNT folder and then double click on System32 5 Scroll to and double click on the file BSDOfcAI d11 The COM Add Ins dialog box reopens and now lists BSD Links for MS Excel BSD CostLink AE User Manual 109 110 Chapter 7 Linking Add Ins avail
17. the Commands list to find COM Add Ins Show All Scripts COM Add Ins Web Options The COM Add Ins command in the Commands list NOTE In the following steps you will click and hold the left button on your mouse Do not release it until the directions say release the mouse button 9 Click and hold on COM Add Ins and drag it to the Tools menu Continue holding down the left mouse button BSD CostLink AE User Manual 107 108 Chapter 7 Linking Your mouse pointer will look like this Tg Mouse pointer when dragging a command to a menu or toolbar 10 As you hold the mouse button down over the Tools menu the Tools menu will open 11 Continue holding the mouse button down as you drag the command to where you would like it listed in the Tools menu A black line will appear where the command will go Continue dragging the mouse until the black line is located where you want the COM Add Ins command You may want to put it near the bottom of the menu since you will probably only use it this one time Tools Y Spelling F7 AutoCorrect Share Workbook Track Changes gt Merge Workbooks Protection b Online Collaboration gt Goal Seek Scenarios Auditing gt Macro Add Ins Vv Customize Options Tools menu showing black line where command will be listed once the mouse is released 12 Once the black line is located where you want COM Add Ins lis

Linking

Contents

Download Pdf Manuals

Related Search

Related Contents