Excel - Data Link Named Range

Last updated: 2022-08-30

Overview

A workflow of utilizing Excel named ranges to produce AutoCAD tables has been developed. Excel contains tools to create named ranges, but this new tool creates the named range in one click and provides efficiency to the process.

It is recommended to have one table per Excel worksheet to take advantage of the automatic naming of the named range in this tool. With this said, this tool does provide flexibility to have multiple tables in one Excel worksheet and have a custom named range for each table.

Usage

The WisDOT Excel Add-In must be installed prior to running the tool.

  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.

Flexibility

Updating existing named ranges

If a table is updated (add a row or column) and the new row or column is added from within the limits of the existing named range, the named range typically will automatically update. On the other hand, it may be best practice to just update the named range using the WisDOT MQ Named Range tool.

  1. Select the updated extents of the table to be included in the named range.
  2. WisDOT Civil 3D Tab > DL Named Range

  3. Since the tool utilizes the worksheet name to name the named range, the tool will ask you how to proceed.

    .

    1.  OK to accept the default name and overwrite the original named range

Multiple tables per sheet

In some cases large data tables are required to exist on one Excel worksheet. To properly display these tables in the plan, they need to divided within that sheet. This tool allows multiple tables/named ranges to exist in one Excel worksheet.

  1. Select the updated extents of the table to be included in the named range.
  2. WisDOT Civil 3D Tab > DL named Range

  3. Since the tool utilizes the worksheet name to name the named range, the tool will ask you how to proceed.

    1. Enter a new name to create a new named range for the second table on that sheet.

Requirements

Requirement:

The first character of a named range must be a letter, an underscore character (_), or a backslash (). This tool accomplishes this by assigning the prefix DL_ to the the name.

Named ranges in Excel are limited in what characters can exist in the name. Characters in the name can be letters, numbers, periods, and underscore characters. Therefore, since this tool utilizes the worksheet name to automatically name the named range, the worksheet name must also follow these character limitations.