The diagram above illustrates the process whereby data is pulled from CostMANAGER into Excel. This is achieved using a SQL Server connection. Data is initially written to a new worksheet where it can be edited. The data is then referenced in the appropriate cells in the predefined import worksheets so that it can be immediately import back into CostMANAGER, updating existing values in the database.
The following steps detail how to configure an import template using an example export that is based on the budget phasing summary level filtered to pull specific items of data. In this case it is manual phasing data for a specified set of budget items and a calendar containing monthly periods.
Note: this method has been verified for Excel 2007 and later.
Step 1: Define an Input Screen
KDE Exports are based on input screen levels. This input screen shows a budget phasing summary level with a single value column applied.
The calendar in use has 36 monthly period applied to the screen and a filter has been created that includes a specific selection of budget items, in this case 6 budget items, all of which happen to be manually phased. This input screen is now the basis of our export in the next step.
Step 2: Define an Export
In the data exchange we create a new export called “Manual Forecast Export”. The criteria includes the layout (or input screen) and filter that we defined in step 1. Note the phasing summary level is selected.
The export data can be viewed from within CostMANAGER in the export data grid to check that the criteria are correct and the expected data is produced. It is important to ensure that all columns required for a valid import are present.
Step 3: Create New Worksheet
Inside the budget importer template we need to create a new worksheet.
I have named this worksheet “Phasing Summary Export” and positioned it next to the manual phasing editing worksheet. This worksheet is for the purpose of pulling data from the predefined export as described in step 2. Data can be edited in this sheet prior to re-import into CostMANAGER.
Step 4: Pulling Export Data into the New Worksheet
Data is pulled from CostMANAGER into the new worksheet. It is not necessary to elaborate on how this is achieved here as it is described fully in the article “How To: Pulling Export Data into Excel“.
The data as seen in the exporter grid in step 2 should now appear in the new worksheet.
Step 5: Configure the Import Worksheet
The next task is to configure the editing worksheet. Firstly unprotect the sheet by selecting the highlighted button on the review menu. This allows us to make changes to the worksheet. It can be re-protected using the same menu later.
New phasing columns need to be added to match the number of periods in the exported data.
Ensure that the format of each cell on the manual phasing editing sheet that will have a reference to cell addresses on the exported data worksheet is ‘general‘.
To format a single cell:
- select the required cell
- right-click to bring up the menu then select “Format cells”
- select the number format as ‘general’.
This can also be done after selecting a range of cells or by using the format painter on the home menu to copy a format from one place to another.
Step 6: Reference the Export Data in the Import Worksheet
The last step in this configuration is to map cells in the Manual Phasing Editing worksheet to the addresses of corresponding data in the new export data worksheet.
In the example below, cell D16 in the Manual Phasing Editing worksheet is mapped to its corresponding cell, F2, in the Phasing Summary Export worksheet as highlighted in the formula bar.
In this case cell D16 is the first of a range of cells in the Manual Phasing Editing worksheet that has to reference data from the new worksheet. The full range of neighboring cells can now be rapidly filled with the relative references to the data being re-imported.
To fill a range of cells containing relative referenced (or addresses), select a cell or range of cells that contain correct references and then drag outside the selection range in order to fill the range of cells with relative references. Alternatively, formulas can be copied and pasted across the required range.
Step 7: Edit Data
Data that has been pulled from CostMANAGER can now be edited in the Phasing Summary Export worksheet. The result of this will be to automatically update the Manual Phasing Editing worksheet.
It is important to Avoid editing the Manual Phasing Editing worksheet as this will overwrite formulas or references. It is recommended only to edit data in the worksheet containing the exported data (i.e. the new worksheet created in Step 3 above).
Steps 1 to 7 completes the process of configuring the import template to edit CostMANAGER data for the first time. If the Excel template is saved in this state then the next time you need to pull data from CostMANAGER for editing all you need do is select the “Refresh All” button on the data menu as highlighted below.
Step 8: Reload Data into CostMANAGER using the KDE
The last step in this process is to reload data back into CostMANAGER using the budget importer. In this example we have modified budget phasing data so the first task is to select the ‘Transfer to Loading’ macro in the manual phasing editing worksheet. This transforms the data into the appropriate format for loading.
In the appropriate configuration within the budget or document importer it is important to select the required option for loading data for value that might already exist (e.g. import new and update existing)
Finally, if you need to modify the shape of the data for an export before pulling it into Excel, e.g. adjusting the filter to include a different set of budget items or changing the phasing periods, you have to login to CostMANAGER, amend criteria on the input screen in question and update the pre-defined filter as required.