How to automatically pull data from Excel or CSV file to another worksheet without VBA macros.

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.
This tutorial will take you through the setup of the ExtractTable automation command. Use it when you need to pull data from one Excel worksheet or from a CSV file to another Excel workbook. This command is useful when you need to fetch data which you will process later on.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2024, 2019, 2016, 2013, 2010, and Microsoft 365.
Get started with XLTools ribbon
Download XLTools for Excel
 – free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the ExtractTable command

Say, you have an Excel table with invoices. Every week or so, the file gets updated. Each time you need to process invoices data and use it in further calculations.
When you automate this process with the ExtractTable command, you can pull data in a button click. Follow the steps below to execute the command yourself.
Feel free to download the sample file used in this tutorial.
Download sample file (xlsx)

1. Insert the ExtractTable command

  1. Let's take a look at the Excel spreadsheet and data you want to extract. In our example, the spreadsheet contains the worksheet "Invoices".
  2. Add a new worksheet → Name it, for example, "AUTO". This worksheet will hold automation commands.
    Tip:
    it's best to keep automation commands on a separate worksheet, not to interfere with data.
  3. Select a starting cell where to place the command, e.g. A1.
  4. Open XLTools tab → In the Automation group, open the drop-down list → Select ExtractTable.
  5. See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to extract data

Once you've inserted the ExtractTable command, specify its parameters. This automation table defines how to extract data from the source worksheet.
XLTools.ExtractTable
Range:
HeadersRange:
Columns:
WorkbookFile:
SkipTopRowsCount:
PreserveFormat:
KeepWorkbookOpen:
ApplyTableName:
TableSortRange:
OutputTo:
What each line in this command reads:
  • Range: take the range from the source worksheet. This is the source range that we'll extract.
  • HeadersRange: specify the headers range if needed.
  • Columns: specify which columns to extract.
  • WorkbookFile: specify the source workbook if different from the active one.
  • SkipTopRowsCount: specify how many top rows to skip.
  • PreserveFormat: choose whether to preserve cell formatting.
  • KeepWorkbookOpen: instruct to keep the workbook open.
  • ApplyTableName: assign a name to the resulting table.
  • TableSortRange: specify sorting if needed.
  • OutputTo: specify where to place the extracted data.
See other available options to use in parameters.

3. Execute the command and review the extracted data

  1. Select the table of the automation command → In the Automation group, click Execute Commands.
  2. Review the extracted data:
    • See that values are extracted from the source worksheet.
    • The resulting table is placed on the output worksheet.
Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.
  1. In the Automation group, click Add Custom Buttons.
  2. Name the button, e.g. "Pull Invoices" → Specify the range of the ExtractTable command → Click Save.
  3. Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.
Note:
each time you execute the command, the newly generated worksheet will replace the previously generated worksheet. So before running the command next time, you can change its name in the OutputTo parameter.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:
  • Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
  • Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.
Below are all parameters and available attributes for the ExtractTable command.
Tip:
select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

Range:

This parameter specifies which data range should be processed.
  • Required parameter, cannot be blank
  • If you refer to a source workbook, it can be open or closed
  • The source workbook must be located in the same destination as the automation file
OptionsMeaning
Sheet1!A1:B2
Range A1:B2 on Sheet1 in the active workbook
Sheet1!A:C
Entire A, B, C columns on Sheet1 in the active workbook
Sheet1!1:3
Entire 1, 2, 3 rows on Sheet1 in the active workbook
Sheet1!
Entire Sheet1 in the active workbook
Sheet1!@Table1
Table1 on Sheet1 in the active workbook
Table1[Column1]
Column1 in Table1 in the active workbook
[Book1]Sheet1!A1:B2
/
[Book1.xlsx]Sheet1!A1:B2
Range A1:B2 on Sheet1 in the workbook Book1. Book1 must be located in the same destination as the automation file.
File.csv
Data from the file File.csv. The file must be located in the same destination as the automation file.
C:\Documents\Workbook.xlsx
Specify the file path to the workbook
Note:
Use standard rules of range referencing in Excel. If names of worksheets or workbooks contain spaces, enclose the name in single quotes. In order for automation commands to identify references, do not use spaces in the names of worksheets, workbooks, or tables.

HeadersRange:

This parameter defines the range that contains headers.
  • Required parameter, cannot be blank

Columns:

This parameter defines which columns to extract.
  • Required parameter, cannot be blank

WorkbookFile:

This parameter specifies the source workbook file.
  • Required parameter, cannot be blank
  • The length of a text string in a cell cannot exceed 250 characters

SkipTopRowsCount:

This parameter defines how many top rows to skip in the source range.
  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
0…10Use a value between 0 and 10
(blank)Same as 0

PreserveFormat:

This parameter defines whether to preserve cell formatting from the source.
  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
TRUEPreserve formatting from the source
FALSEUse default formatting
(blank)Same as FALSE

KeepWorkbookOpen:

This parameter defines whether to keep the resulting workbook open after the operation.
  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
TRUEKeeps the workbook open
FALSEDoes not keep the workbook open
(blank)Same as FALSE

ApplyTableName:

This parameter assigns the name to the resulting table. The result is generated as a named formatted table.
  • Required parameter, cannot be blank
  • The length of a text string in a cell cannot exceed 127 characters
OptionsMeaning
Table1Assign any name to the resulting table

TableSortRange:

This parameter defines whether to sort the resulting table.
  • Optional parameter, you can leave it blank or remove from the command altogether

OutputTo:

This parameter specifies where to place the extracted data.
  • Required parameter, cannot be blank
  • The length of a text string in a cell cannot exceed 255 characters
OptionsMeaning
NewSheet[Result]Place the result on a new worksheet named Result in the active workbook
NewHiddenSheet[Result]Place the result on a new hidden worksheet named Result in the active workbook
ExistingSheet[Result!A20]Place the result on an existing worksheet named Result in the active workbook, place the results starting in cell A20
NewWorkbook[Result.xlsx]Place the result into a new workbook named Result.xlsx. The workbook will be saved in the same location as the automation file.
Explore other automation commands
Any questions or suggestions?
How can we help?