Earthwork Converter

(Utility for importing Earthwork.txt file into Excel)

Syntax at the bottom of the seed earthwork input file (V8_EARTHWORK.inp) and seed earthwork run (available using Geopak Project Manager) 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.

More Information:
- using the earthwork input file.
- using the seed earthwork run.

The earthwork input file can be found on the server under X:\V8_Resource\X_30\Standards\Input_Files\

The seed earthwork run can be found on the server under X:\V8_Resource\X_30\Standards\Seed_runs\

The Excel workbook WFL_earthwork.xls can be found on the server under X:\V8_Resource\X_30\Standards\Bin and is composed of 3 worksheets:

• Instructions
• From Geopak
• Adjustments

The “Instructions” worksheet provides instructions for using the Workbook.

NOTE:
Your MSExcel security may be set too HIGH to run macros. Before attempting to run this spreadsheet, select Tools > Macro > Security and set the security to Medium.

The “From Geopak” worksheet is used only for inserting and updating the text file 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. Follow these steps:

1. In the From Geopak worksheet press the CLEAR Contents button (see below). This cleans all data out of the worksheet and turns off the cell protections.
   
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:
   
3. Browse to the location of your column based ASCII file, earthwork.txt. Highlight the file and click on Open as shown below:
   
4. Adjust the Original data type to Delimited and set the Start import at row: to row number 3. Click Next >, as shown below:
   
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 Finish, as shown below:
   
6. The Import Data dialog will appear as shown below. Assure that the toggle Existing worksheet: is selected and that the cell selected is A7. Click OK as shown below:
   
7. The data will import into the worksheet From Geopak, however not all the data will be visible. Press the FIT Contents button to resize the columns and set the protections as shown below:
   
   The result is shown below:
   
   

This completes the process of importing the earthwork input file into the From Geopak worksheet. The column totals shown in Row 4 may be checked against your Geopak earthwork log file.

To refresh the data in the worksheet, press the button, REFRESH Data. The macro will ask what file should be imported. By default the existing file will be chosen. Either select this file or find a different earthwork input file to import.

The “Adjustments” worksheet is used for entering shrink/swell factors, inserting (or deleting) rows for Added Quantities, and creating Sub-totals for Plan/Profile sheets. A fragment of the results (from the "Adjustment" worksheet tab) are shown below:

All data from the From Geopak worksheet is copied over to the Adjustments worksheet. Additional columns are also set up for manipulating the earthwork volume data. The additional columns include:

• SUBTOTALS for UNADJUSTED Roadway Excavation volumes.
• SUBTOTALS for Embankment volumes.
• SUBTOTALS for Subexcavation volumes.
• SUBTOTALS for Topsoil Excavation volumes.
• Shrink/Swell factors.

• Highlight the range of cells to be subtotalled, (i.e. B7:B22).
• Click on the Σ button and the subtotal will appear in the column adjacent to the last cell selected, (i.e. C22) as shown below.

A generic shrink/swell factor may be entered on the spreadsheet (see cell D2) that relates to UNADJUSTED Roadway Excavation. Once this value is changed the entire D column inherits this value. 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 Column E total is recalculated.

For example, note that Station 50+140 through 50+260 uses a factor of 1.0 to calculate the Adjusted Rdwy Ex. The values in B14:B20 equals E14:E20.

• The macro unprotects the worksheet.
• Adds a row where the cursor is positioned.
• Makes some security adjustments.
• Copies the shrink/swell factor for the volume just above it, rather than use the generic value.
• Runs a formula where the manually entered value in row B is multiplied by the shrink/swell factor in row D and puts this calculated value in row E, (Adjusted Rdwy Excavation).
• Changes the color of the cell in row A to white in order to easily distinguish where the rows were added.
• Protects the worksheet.

• The macro unprotects the worksheet.
• Deletes the entire row.
• Protects the worksheet.