Earthwork quantities sheet

The following workflow utilizes Excel named ranges, Civil 3D Data Links and a custom Civil 3D table style to stylize and place Earthwork Data tables in the Civil 3D plan sheet. The resulting plan sheets contain searchable text. Updates to the earthwork data tables are relatively straight forward when using the latest tools, named ranges, and data links.

Create the sheet file and layout tabs

1. Review the FDM for file naming conventions: https://wisconsindot.gov/rdwy/fdm/fd-15-05-att.pdf#fd15-5a3.3
2. Complete the Earthwork quantities volume calculation Earthwork quantities
3. Create Earthwork details file.
   1. WisDOT Sheets > Support > Palettes ON/OFF
   2. WisDOT Sheets > Sheet Creation > Earthwork
   3. Startup Main tab > Earthwork Data Speed Sheet
   4. Save file in Project ID\sheets\090101-ew.dwg
   5. Save

Excel file and prerequisites

Open Earthwork Quantities Spreadsheet that was created using workflow here: Earthwork quantities

1. Project ID\dsgn\qty\ewrk\ewrk-##.xlsx

Storage location

Store Excel Earthwork files in the Civil 3D Project directory, under ProjectID\dsgn\qty\ewrk. The Data Link tool works best with a relative path so storing the Earthwork Excel files in the Project Directory is of priority.

Merge & Center

Cells that contain text that is too large to fit within that cell must be expanded. Use the Merge & Center command followed by the Left Align tool to merge multiple cells and allow the text to fit within a single cell.

Gridlines

If grid lines are visible in Excel, they will print from Civil 3D. In Excel View Tab > Show Panel > Uncheck Gridlines

Video: pln-prod-erthwrk-qnty-02.mp4 4:10

Any columns in the earthwork data sheets that contain all “zeros” can be deleted from the final plan sheet.

1. Select the column(s) heading to be hiddendeleted

2. Right Click > Delete

Tip: Following the process of delete earthwork detail sheet columns, the earthwork summary worksheet will contain columns that are all zeros or contain text that reads “RangeNotFound”. The columns that contain all zeros or “RangeNotFound” of the earthwork summary sheet can be deleted. If there are columns that need to remain but contain cells that read “RangeNotFound”, the cell can be overwritten with a zero (0). The warning “RangeNotFound” just means the cells (range) within the earthwork detail sheet no longer exists (deleted). The value can be changed to 0, since that is really what it is.

Info: After this process, all data remaining in the earthwork detail and summary sheets should be intact. If any cells contain errors, undo your work and re-correct the work to remove any calculation errors. If done properly, the remaining cells should be error free.

The corresponding notes that are not applicable can also be removed from view.

1. Select the notes/cells that are not applicable
2. Right Click > Delete
   1. Choose Shift Cells Up

To create the earthwork quantities tables in Civil 3D, a named range is required for each quantities table in Excel. This allows a link to be created between the Excel file and Civil 3D drawing. The following workflow utilizes a custom WisDOT tool to create named ranges.

Requirement: The WisDOT Excel add-in must be installed to run the Data Link Named Range tool. For external partners, the WisDOT Excel add-in is available on the Wisconsin Department of Transportation Civil 3D file descriptions (wisconsindot.gov). Internal users should see Autodesk Civil 3D 20XX -Excel Add-In in Software Center.

See complete tool documentation here: Excel - MQ Named Ranges (wi.gov). The basic workflow to create named ranges follows:

1. In Excel, select the range of cells you wish to create a named range from.
2. WisDOT Civil 3D Tab > Data Link Named Range
3. A named range will be created using the Excel worksheet name.
   1. Naming Convention: DL_Worksheet_Name
   2. All spaces are replaced with underscores(_)
   3. Special characters are not allowed in the Excel named ranges. Only use letters, numbers, periods, and underscore characters in the sheet name.
4. Repeat for each table to be displayed in Civil 3D.
5. For additional tables contained on a single worksheet, a window will open allowing a custom named range to be entered.
6. Re-run the tool to overwrite an existing named range.

Create named range for notes

Prior to creating a table in Civil 3D, a named range for the notes table needs to be created in Excel.

1. Create a preliminary named range for the sheet
   1. Select cell A1
   2. WisDOT Civil 3D Tab > Data Link Named Range
   3. This creates the first named range on a sheet and then allows us to specify custom names for following named ranges on a sheet.
2. Select the range of cells for the notes tables
   1. Approximately Y11:Z22.
   2. The third column is typically only designer notes and should not be shown on the plan.
3. WisDOT Civil 3D Tab > Data Link Named Range
   1. Since a named range from step 1 has already been created for the sheet (Step 1) a window will appear, asking for a custom name (or if it is desired to overwrite the previously created named range)
   2. Enter a custom name for the notes table: DL_SheetName_Notes

Create named ranges for earthwork data

1. Select the range of cells for the first earthwork data table
   1. Approximately Row1:Row51.

      Tip: Approximately 45 rows of data will fit on one earthwork data sheet

2. WisDOT Civil 3D Tab > Data Link Named Range
   1. A named range is created for the cells selected.
   2. If a range has already been created for that sheet, apply a suffix to the name: DL_SheetName_2
3. Save the Excel File

Before making additional named ranges, it is wise to test the initial named range to make sure it fits on the plan sheet. This work utilizes the DOT Create Data Links and Tables tool. Full tool documentation can be found here: Sheet Tools - Create data links and tables (wi.gov)

1. Open Sheets\090101-ew.dwg
2. WisDOT Sheets Tab > Sheet Creation Panel > Sheet Tools > Create Data Links and Tables

   or Command Line:DOTCreateDataLinksAndTables

   1. Data Links tab : Create Data links for the tables
      1. Click the to select the Excel File containing the tables to be inserted.
      2. Use Filter to filter the named ranges if desired
         1. For Example DL*
      3. From the list, select all named ranges to be made into a data link. One data link is required for each table that will be created in Civil 3D.
         1. Utilize Ctrl+Select and/or Shift+Select to select the named ranges desired.
         2. Click Select All to select all named ranges and Deselect All to deselect all named ranges.
      4. Leave the default Path Type and Settings.
      5. Click OK to create the Data Links

         

   2. Tables tab
      1. Select the first earthwork data table (DL_8_1#EWK).
      2. Table Style: WisDOT Standard is the WisDOT default and should be used.
      3. Click OK to place the first table.
      4. Click a location in model space within the Layout 1 viewport to place the table(s). With the dialog box open, users can also adjust the location of tables already placed in model space.

         

      5. Repeat for the Notes table (DL_8_Notes#EWK).
3. Adjust the location of the first earthwork data table and note table to make sure both can be situated to fit within one view port.
4. If adjustments to the named range are to be made, make a note and return to the spreadsheet to adjust.

Video: pln-prod-erthwrk-qnty-05.mp4 5:12

1. Return to the earthwork data spreadsheet
2. If required, adjust the existing named ranges
   1. Select the range of cells for the first earthwork data table to be adjusted
   2. WisDOT Civil 3D Tab > Data Link Named Range
   3. Enter the name of the range to be updated.
3. Duplicate the data sheet headers for addition earthwork data tables/sheets
   1. Select Rows 1:6 > Right Click > Copy
   2. Right click the row below previous data table > Insert Copied Cells
   3. Add one more row above the duplicated header to provide a blank space
      1. Right-click the top row of the duplicated header > Insert
   4. Duplicate the header for all earthwork data sheets.
4. Create named ranges for any remaining earthwork data tables and sheets.
   1. *Approximately 40-45 rows of data (not including headers) will fit on one
   2. Select the range of cells for the remaining earthwork data tables
   3. WisDOT Civil 3D Tab > Data Link Named Range
   4. Enter the name of the ranges remaining, remember to apply a suffix that makes sense to the users
      1. DL_8_2
      2. DL_8_3
      3. DL_Gatewood Heights
   5. Remember, a sheet that does not contain a previous named range will default to a named range of DL_Sheet name**.
5. Save the earthwork data spreadsheet.

1. Return to 090101-ew.dwg.
2. WisDOT Sheets Tab > Sheet Creation Panel > Sheet Tools > Create Data Links and Tables
   1. Data Links tab
      1. Create data links for any remaining named ranges that have not already been created
   2. Tables tab
      1. Create tables within subsequent layout view frames for any remaining data links
3. Adjust the earthwork data tables to fit within the view frame.
   1. For the most part, the height of the table as initially created is at it’s minimum and does not require adjustment. The width, on the other hand, will need to be lessened to fit within the view frame.
   2. Best practice is to window select the table and use the triangle grip in the lower right to adjust the table size.
      1. The benefit of this grip is that it will not allow the table to be shrunk vertically beyond a minimum height based on the data contained.

      2. 

   3. If desired, individual column widths can be adjusted using the various grips within the table.

Video: pln-prod-erthwrk-qnty-07.mp4 9:51

If there are design changes and the earthwork requires recomputing, these changes can be pushed back into the final Earthwork Data plan sheets.

1. Follow the Earthwork quantities training to update the earthwork detail Excel worksheets and workbooks
2. Prepare the affected detail worksheets for the plan sheets
   1. Delete columns with no data (all zeros)
3. Update named ranges for the affected detail worksheets.
   1. Select the range of cells
   2. WisDOT Civil 3D Tab > Data Linked Named Range
   3. Overwrite the named range OK
   4. Make sure the named range is exactly the same as it was before the updates

4. Save the Excel File

Civil 3D data links and tables do not automatically update after the Excel file is saved or the DWG is opened. Once the DWG is open, the data links update process needs to be manually initiated. Prior to updating any tables or data links in Civil 3D, the Excel file must be saved.

Option A:

1. Annotate tab > Tables > Download from Source
2. Select which tables to update or choose all data links from the command line.

Option B:

1. Select the table to be updated > Right Click > Update Table Data Links

Option C:

1. Click one specific cell in a table > contextual ribbon > Download from Source

Display of table gridlines in Civil 3D

When placing the tables in model space in Civil 3D, even though the gridlines are turned off in Excel, the AutoCAD table gridlines will still display.

Once the table is actually printed to PDF the gridlines will not appear, but the formatting for cell borders set within Excel will show in the PDF.

If desired to turn off the gridlines, the layer Defpoints can be turned off.

1. Home Tab > Layers Panel > Layers drop-down > Turn off Defpoints

   

Add the sheets to a sheet set

1. Add sheets to sheet set manually #add

Dataset: pln-prod-erthwrk-qnty-end-data-c3d22.zip

1. Review the FDM for file naming conventions: https://wisconsindot.gov/rdwy/fdm/fd-15-05-att.pdf#fd15-5a3.3
2. Complete the Earthwork quantities volume calculation Earthwork quantities
3. Create Earthwork details file.
   1. WisDOT Sheets > Support > Palettes ON/OFF
   2. WisDOT Sheets > Sheet Creation > Earthwork
   3. Startup Main tab > Earthwork Data Speed Sheet
   4. Save file in Project ID\sheets\090101-ew.dwg
   5. Save

1. Earthwork Quantities Spreadsheet was created using workflow here: Earthwork quantities
2. Open the Excel spreadsheet from Project ID\dsgn\qty\ewrk\EWK-25.xlsx
3. In Excel, File > Options > Formula Tabs > Error Checking Rules. Uncheck both Formulas which omit cells in a region, and Unlocked cells containing formulas. OK

   Info: This step is important since it turns green arrow error markers in cells off. The green arrow markers will appear in most cells of the Civil3D linked table causing confusion and clutter.

4. Switch to the View tab of the ribbon > Show panel > Uncheck the Gridlines option

Any columns in the earthwork data sheets that contain all “zeros” can be hidden from the final plan sheet

1. Select the column heading to be hidden
2. Right Click > Hide

The corresponding notes that are not applicable can also be hidden from view.

1. Select the notes/cells that are not applicable
2. Right Click > Delete
   1. Choose Shift Cells Up

1. Open the EWK-25.xlsx file in Excel.
2. Scroll to the right to see the Notes area.
   1. Widen the X, Y, and Z columns so all the text in these cells is readable.

      Info: Column AA are designer notes and are not shown on the plan.

3. Select the remaining notes, right-click > Copy

   Info: Column AA are designer notes and are not shown on the plan.

4. In Civil 3D, open 090101-ew.dwg.
5. Home ribbon > Clipboard panel > Paste pull-down > Paste Special
   1. In the Paste Special dialog select the Paste Link button
   2. Select Microsoft Excel Worksheet
   3. OK
6. Place the frame of the Notes table at the bottom of the first view frame.
7. Copy the first Notes Table from the first view frame to several following view frames.

1. In the Excel spreadsheet > Earthwork Details Tabs
2. Select the cells within the Earthwork Details

   Info: It approximately 45 rows (not including headers) will fit on a standard Earthwork Data sheet.

3. Right-click > Copy
4. In Civil 3D, open 090101-ew.dwg.
5. Home ribbon > Clipboard panel > Paste pull-down > Paste Special
   1. In the Paste Special dialog select the Paste Link button
   2. Select the Microsoft Excel Worksheet
   3. OK
6. Move the OLE frame into the frame of Layout 1. Click to place it inside the frame.

1. Inspect where the data grid falls outside the Layout 1 frame or over the Notes table.
   1. If the frame is too wide
      1. Return to the Excel spreadsheet and adjust the columns to fit the table inside the frame.
      2. Save the file.
      3. Return to the 090101-ew.dwg file and notice the change to the inserted table. You may need to move the table to fit it more appropriately on the sheet.
   2. If the frame is too long
      1. Find the last full row that will fit on the sheet without overlapping the Notes table. Remember the station of the row that will be the last for the page.
      2. Approximately 45 rows of data (not including headings) will fit in the standard Earthwork details sheet.
2. Duplicate the column headings for each Earthwork Layout
   1. Return to the Excel spreadsheet.
   2. Select rows 2 through 6 (Headings) > right-click the rows > Copy
   3. Scroll down to the table station that will fit on one page.
   4. Select the ROW BELOW that station’s row, Right-click > Insert Copied Cells.
   5. Repeat this for any remaining sheets
      1. Approximately 45 rows of data will fit on one Earthwork Data Sheet.

1. Copy and paste the adjusted table data into the frames.
   1. Select first series of earthwork data > right-click > Copy
   2. Return to the 090101-ew.dwg.
   3. Delete the original table that was placed in the Layout 1 frame.
   4. Select rows 2 through 6 (Headings) > right-click the rows > Copy
      1. In the Paste Special dialog select the Paste Link button
      2. Select the Microsoft Excel Worksheet.
      3. OK
   5. Place the table frame at the top of the Layout 1 frame.
   6. Return to the Excel spreadsheet.
   7. Select next series of earthwork data > right-click > Copy
   8. Return to the 090101-ew.dwg.
   9. Home ribbon > Clipboard panel > Paste drop-down > Paste Special
      1. In the Paste Special dialog select the Paste Link button
      2. Select the Microsoft Excel Worksheet.
      3. OK
   10. Place the table frame in the Layout 2 frame
   11. Repeat for remaining Earthwork Data

1. Copy the first Notes Table from the first view frame to any remaining view frames.

1. Add sheets to sheet set manually #add