Toad World® Forums

How do a add a named range in my XLS so that Toad can read it...


#1

…without resorting to using the System Tables view?

I tried google, ms help, and didn’t come up with much!

It would also be nice if the export to XLS supported this as an option so I could create spreadsheets and then open back up for querying with as little work as possible.

Jeff


#2

Jeff,

In MS Excel 2007, select a range of cells, right-click and select “Name a Range.” Fill in the details and save the file as XLS or XLSX. You’re done.

In MS Excel 2003, select a range of cells and navigate to the INSERT | Name | Define menu item. Enter a name for your range and then save it as an XLS file.

You can see the two attached filed for screenshots of these actions.

Once you have these named regions defined, they open quite nicely in Toad for Data Analysis. You’re right - querying the XLS data in a tool like Toad is much easier than trying to filter in Excel.

Daniel
xls 2007 named region.jpeg


#3

Jeff,

In MS Excel 2007, select a range of cells, right-click and select “Name a Range.” Fill in the details and save the file as XLS or XLSX. You’re done.

In MS Excel 2003, select a range of cells and navigate to the INSERT | Name | Define menu item. Enter a name for your range and then save it as an XLS file.

You can see the two attached filed for screenshots of these actions.

Once you have these named regions defined, they open quite nicely in Toad for Data Analysis. You’re right - querying the XLS data in a tool like Toad is much easier than trying to filter in Excel.

Daniel
xls 2003 named region.jpeg


#4

A HA! It turns out I had figured it out, but something else tripped me up before I could have my eureka moment.

When I connect to Excel, I specify a sheet.

Unfortunately, the ‘Object Pallete Data Source’ selector doesn’t default to the sheet I connected to. Instead, it shows me the first XLS file in that directory. Am I doing something wrong?

Once I select the spreadsheet I wanted to connect to, then I see the name regions quite nicely!

See attached screenshot for what I’m referring to.
object_pallete_dropdown.jpeg