This Analytics Edge wizard allows you to read an Excel worksheet into your macro. The worksheet can be in any open workbook, and it is possible to read part of a worksheet if desired.
You can use this function to read data downloaded or imported form other data sources, or to consolidate data from other workbooks.
Workbook/Worksheet – select the workbook/worksheet combination you want to read from. Workbooks must be open to record the macro, but do not need to be open to run the macro.
Read the entire worksheet – select this option if the data is located starting in cell A1, and the whole worksheet is data.
Read a table in the worksheet – select this option, and pick a top left cell, to read a table located somewhere in a worksheet that may contain other data. The table must contain continuous data in the first row and column (no blanks).
Read a specific region only – if the data might contain blank cells, select this option and identify the entire range of cells to be read.
Data does not include a header – check this if the top row of data is not the column headers (column names).
Assign range names to each column – assigns Analytics Edge range names to each column that is read in. These range names can be used in the place of fixed values in other Analytics Edge wizards by enclosing them in square brackets [Column Name].
Load direct to a named table – this option lets you load a reference table from a worksheet in one step, as opposed to loading it and using Table Name to establish the reference.
Data is transposed – if the data is oriented by rows instead of columns, check this option to transpose the data as it is read in.
Blog article : Using the Read Worksheet Function
Updated for v10.