Toad World® Forums

Linking multiple named range Excel tabs in same workbook



I have an Excel workbook which holds a number of spreadsheet tabs (all with the same set of columns) each tab is a daily extract from a third-party systems (I don’t have direct access to query this database).

What I want to be able to do is create a SQL query that can combine all the data from all the tabs and filter out specific information.

I have created a named range for each tabs, so TDA recognizes these as tables.

But I am unsure how to link these tables together. Must I create a select statement for each and use a UNION ALL, or is there another way?

Also I may in time need to link two or more Excel workbooks containing this data, as we may start a new workbook at the beginning of each financial year.

Any help and advise welcome

Thank you in advance




Yes. You will need to union the worksheets then treat them as sub select to do the filter. See below. In the TDA Beta the Query Builder has a new Union operator that makes this easier to build.

select * from (SELECT “Set1”.“Col1” AS a
, “Set1”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set1” “Set1”
(SELECT “Set2”.“Col1” AS a
, “Set2”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set2” “Set2”
SELECT “Set3”.“Col1” AS a
, “Set3”.“Col2” AS b
FROM “C:\Documents and Settings\dpeabody\My Documents\Fruit”.“Set3” “Set3”))
where a > 6;