I have recently added syntax at the bottom of the seed earthwork input file (V8_EARTHWORK.inp) and seed earthwork run (available using Geopak Project Manager).
The earthwork input file can be found on the server under F:\V8_Resource\X_30\Standards\Input_Files\
Note: Click here for information about using the "earthwork input file".
The seed earthwork run can be found on the server under F:\V8_Resource\X_30\Standards\Seed_runs\
Note: Click here for information about using the "seed earthwork run".
This syntax creates a “column based ASCII file” that can be imported into Excel and manipulated for providing adjusted earthwork volumes and creating sub-totals of various earthwork volumes for placement at the bottom of Plan/Profile sheets.
The Excel workbook WFL_earthwork.xls can be found on the server under F:\V8_Resource\X_30\Standards\Bin and is composed of 3 worksheets:
The “Instructions” worksheet:
This worksheet is just an attempt to provide built-in instructions on using the Workbook.
The “From Geopak” worksheet:
This worksheet is simply used for inserting the text file (nothing else) that is created when your earthwork input file (or earthwork run) is processed. Totals for the various volumes are provided at the top of this worksheet for a quick check against your earthwork “.log” file.
The “Adjustments” worksheet:
This worksheet is where the power lies and where all the adjustments are made, such as:
NOTE: Chances are your EXCEL security is set too HIGH to run macros. Before attempting to run this spreadsheet, Select Tools > Macro > Security and set the security to Medium.
Step 1): Click on the From Geopak worksheet (at the bottom of the Workbook) and click on the Click here to CLEAR the CONTENTS of THIS worksheet! button, as shown below:
Step 2): Import the text file that was created when you ran your earthwork from Geopak. This file should be in your working directory and should be named earthwork.txt unless you have changed the name in the earthwork input file (or run).
To import the file, make sure your cursor is positioned on cell A7, use the Excel pulldowns, Data > Import External Data > Import Data , as shown below:
Step 3): Browse to the location of your column based ASCII file, earthwork.txt. Highlight the file and click on Import as shown below:
Step 4): Assure that the Original data type is set to Delimited , adjust the setting, Start import at row: so that it starts at row number 3, and assure that the File origin is set to Windows (ANSI). Click Next >, as shown below:
Step 5): The dialog below will appear. This dialog allows the delimiters to be adjusted. Assure that Space and Treat consecutive delimiters as one are checked and click Next >, as shown below:
Step 6): The dialog below will appear as shown below. Accept the default settings and click on Finish, as shown below:
Step 7): The Import Data dialog will appear as shown below. Assure that the toggle Existing worksheet: is selected and that the cell selected is A7, (see Step 2 on how to have this cell be selected automatically). Click on Properties… as shown below:
Step 8): When Properties… is pressed the External Data Range Properties dialog will appear as shown below. I have found that the only setting that needs to be adjusted for what we need to do is the one circled in red below.
You must select Overwrite existing cells with new data, clear unused cells for the data to import into the correct cell range. Click OK as shown below.
Step 9): When OK is pressed on the External Data Range Properties dialog, the Import Data dialog will appear again as shown below. Click OK as shown below:
Step 10): The data will import into the worksheet From Geopak, but will be a bit crowded together, as shown below:
Step 11): Click on the FIT Contents button located in the cell range A2-A4.
This button runs a macro that:
The result is shown below:
At this point you are finished with the worksheet From Geopak. You can check the column totals shown in Cells B1 through F1 against your Geopak earthwork log file.
To clear the contents of this worksheet, simply click on the button, Click here to CLEAR the CONTENTS of THIS worksheet!
This button runs a macro that:
NOTE: This completes the required steps to get the column based ASCII file imported into the worksheet From Geopak. Once again, nothing more is to be done in this worksheet. All manipulation is to be done in the Adjustments worksheet. Please read on to see how that is done.
Once you have successfully completed the 11 steps for importing the column based ASCII file into the worksheet From Geopak, click on the worksheet tab "Adjustments".
A fragment of the results (from the "Adjustment" worksheet tab) are shown below:
The worksheet Adjustments allows for all the columns of data (from worksheet From Geopak) to be copied over. Additional columns are also set up for manipulating the earthwork volume data.
The additional columns include:
Using Subtotals:
Subtotals are typically required for defining areas of excavation/embankment and subsequent placement at the bottom of the Plan/Profile sheets.
An example of using Subtotals is shown below, where I wanted a subtotal of Unadjusted Roadway Excavation from Station 50+000 to Station 50+300.
The required steps for using Subtotals are as follows:
button (located in cell C5) and the subtotal will appear in the column adjacent to the last cell selected, (in my case C22) as shown below: Using Shrink/Swell factors:
Normally, various soil types are encountered when constructing the project. The Geotechnical report usually defines where these areas are. As a result, the material encountered may have varying shrink or swell factors. Sandy material will shrink and rocky material will swell.
In cell D3 you may enter a generic shrink/swell factor that relates to UNADJUSTED Roadway Excavation. Once this value is changed the entire D column inherits this value.
NOTE: It is possible to change this shrink/swell value as necessary in isolated cell ranges within column D to reflect other soil types. The results automatically appear in column E, (ADJUSTED Roadway Excavation) and the total in cell E1 is recalculated. An example is shown below:
Notice that the Station 50+140 through 50+260 uses a factor of 1.0 to calculate the Adjusted Rdwy Ex. The values in B14:B20 = E14:E20.
Using the INSERT and DELETE row buttons:
Many projects require that additional areas or “Added Quantities” be added to the station ranges for placing the quantities on the Plan/Profile sheets.
Since this workbook and its worksheets are protected (by default), it is not possible to simply insert a row and enter additional volumes. It is also not possible to simply delete an added row, where an added quantity had previously been added. As a result, I’ve created 2 additional buttons and assigned macros to them to do the following:
