Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
This lesson explores connecting to an external workbook to append all tables, worksheets and/or ranges within it.
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.Append objects in an external Excel workbook.xlsx
19.5 KB Append objects in an external Excel workbook - Completed.xlsx
Append Objects in an External Excel Workbook
An overview of appending multiple objects in another Excel workbook.
When to use
When you have multiple objects (Tables, Named Ranges or Worksheets) in another Excel file that you would like to combine into a single table.
- Create a new query --> From File --> From Excel Workbook --> browse for the file to import
- Select the folder (the root of the Excel file) and click Edit
- A table of available objects is created
Building the query
- Filter the Kind column to a single type of object (worksheet, named range or table)
- Filter out any objects that are not part of the append
- Remove any columns that are not needed
- Expand the column of available objects
- Apply transformations as required
- Set data type for each column
- Rename the query
- Close & Load the query
Hints & tips
- Connecting to the root file instead of the individual objects ensure that data from new objects gets added to the query when refreshed
- You CAN import these objects from an external workbook:
- Named Ranges
- You CANNOT import these objects from an external workbook:
- Dynamic Named Ranges
- When importing worksheets you may wish to prevent new columns from being added to your output. To do this, select the columns you need --> right click the column header --> Remove Other Columns
Lesson notes are only available for subscribers.