Miscellaneous quantities sheets

The paste link method of pasting an Excel table into Civil 3D does not result searchable text within the PDF of the miscellaneous quantities sheets. The quantities tables end up being raster images in the final printed product. Also, at times, the paste link method can be unstable. The following workflow utilizes Excel named ranges, Civil 3D Data Links and a custom Civil 3D table style to stylize and place MQ tables in the Civil 3D plan sheet.

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-misc-qnty-02.mp4 4:45

To create the miscellaneous 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.

Create named ranges for quantities tables

See complete tool documentation here: Excel - Data Link Named Range (wi.gov)

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 other quantities tables and worksheets.
   1. If multiple tables exist on one Excel worksheet, a custom named range dialog box will appear. Enter a custom name for the table.
5. Save the Excel File.
6. Repeat for other quantities Excel workbooks.

Once the named ranges are established in the Excel Workbooks, they need to be linked to the Civil 3D miscellaneous quantities file. Civil 3D data links will be utilized to create this link.

Create quantities drawing

1. WisDOT Sheets Tab > Sheet Creation Panel >
2. From the palette, click 03- Misc Quantities Speed Sheet
3. A new drawing will be created from the 03-MQ-SPSHT.dwt template.
4. Save the drawing in the sheets folder, 030201-mq.dwg, or following FDM File Naming Conventions.

Create data links

See complete tool documentation here: Sheet Tools - Create data links and tables (wi.gov)

WisDOT Sheets Tab > Sheet Creation Panel > Sheet Tools > Create Data Links and Tables

or Command Line: DOTCreateDataLinksAndTables

1. Data Links tab
   1. Click the to select the Excel File containing the tables to be inserted.
      1. The named ranges list will populate with all named ranges within the Excel File.
   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. Use 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. Path Type: Relative Path should be chosen if the Excel file is within the Civil 3D project
      1. It is recommended to always store the Excel file within the Civil 3D project
   5. Settings (Recommended to leave defaults)
      1. Allow writing to source file: This allows the source Excel file to be directly edited from Civil 3D
      2. Use Excel Formatting: All formatting established in the Excel file will be displayed in the Civil 3D AutoCAD table
   6. Click OK to create the Data Links
      1. Once finished, Process Complete will show at the bottom of the dialog.

      2. 

Now that the link from the Excel sheet has been made to the Civil 3D drawing, a table can be created in Civil 3D. The table data link will create an AutoCAD table in model space while consuming formatting and the table data through the data link from the Excel sheet. Some formatting of the table comes from the Civil 3D table style which is why it is important to use the WisDOT Standard table style.

Dynamic Relationship: The link between the Excel file and Civil 3D drawing is maintained and is dynamic. The table can be edited in Civil 3D and changes pushed back to the Excel file, or the Excel file can be updated and consumed in the Civil 3D drawing.

WisDOT Tool: Create Data Links and Tables

See complete tool documentation here: Sheet Tools - Create data links and tables (wi.gov)

1. Tables tab
   1. Once data links are created in the drawing, the Tables tab list will populate with all data links that are available. This tab is used to create the tables in model space.
   2. Use the Filter to filter or search the list if desired.
   3. Select which data links to be made into tables in Civil 3D.
      1. Users have the choice to place one, multiple, or all the tables in one action.
   4. Table Style: WisDOT Standard is the WisDOT default and should be used.
   5. Click OK to place the tables selected from the list.
      1. If multiple datalinks are placed, the tool will build the tables in one single row
   6. Click a location in model space to place the table(s). With the dialog box open, users can also adjust the location of tables already placed in model space.

   

Adjust the table dimensions

When inserted, the row and column widths do not exactly match the formatting set within Excel. While the table style mostly takes care of row height, the column width needs to be manually adjusted. Adjusting the table utilizing the grips does not affect the display of the text contained within the table.

1. Select the table boarder.
2. Select one of the grips to adjust the column widths. 
   1. The square grips on the interior will adjust the column widths.
   2. The triangle grip on the top right will adjust the overall table width and adjust the columns accordingly. This may be the best option for most tables.

   

Info: Updating data links and tables will override any table dimension adjustments completed in Civil 3D. It is best to format tables in Excel as best as possible and leave the table dimensions after they are built in Civil 3D.

Updates to a data link and table

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.

Info: Updating data links and tables will override any table dimension adjustments completed in Civil 3D. It is best to format tables in Excel as best as possible and leave the table dimensions after they are built in Civil 3D.

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

Tip: Every now and then, and especially prior to plotting to PDF, it is recommend to use Option A along with all data links to make sure all data links are updated to the latest version from the Excel File.

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.

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

pln-prod-misc-qnty-06.mp4 3:42

1. Civil 3D App menu > New

   1. C:\wisdot\Stnd\C3D20XX\Templates\Sheets\03-MQ-SPSHT-wdot22.dwt Open

      There are 20 preset rectangles in modelspace associated with 20 layouts. Instructions for using the file are above the sheet rectangles.

2. Civil 3D App menu > Save As
   1. ProjectID\SheetsPlan\030201-mq.dwg Save
3. Zoom to rectangle labeled LAYOUT1
4. In Excel
   1. Open Excel spreadsheet with table of miscellaneous quantities.
   2. Format the content with Font: Calibri Light, Font Size: 8. This will make the excel content mirror how it looks once pasted into Civil 3D.
   3. Make any visual changes desired in Excel (turn off gridlines, resize column widths, wrap text in cells)

pln-prod-misc-qnty-07.mp4 4:31

Tip: It is recommended to store Miscellaneous Quantities Excel spreadsheets in <ProjectID>\Design\Quantities\MQ within the Civil 3D project.

1. In Excel
   1. Select the range of cells to be placed in the plan sheet.
   2. Copy the range to the clipboard (Ctrl+C or Copy from the Microsoft Excel Ribbon)
2. Activate Civil 3D
3. Home tab > Clipboard panel > Paste drop-down > Paste Special
   1. Paste Link: checked
   2. As: Microsoft Excel Worksheet
   3. OK
4. Digitize the location to place the table. If the table is to big for the view frame, re-organize/split the table in excel and re-copy to Civil 3D. Do not resize/scale the table in Civil 3D to fit.
5. If rows and/or columns of content are added in Excel, the Paste Link will not increase in size. It is easiest to delete the existing Paste Link and recreate it from Excel.
6. Borders will show around the Paste Link objects in the layouts. These borders will not plot.
7. Add the layouts to the project sheet set for automatic update of the layout title block information and publishing to pdf. See Add sheets to sheet set manually #add for instructions.