Note: An on-line training module has been created specifically highlighting this special process. See here for details:
www.softinterface.com/Convert-XLS/Documentation/
This special process can copy the data (values/formula) you specify from one worksheet to another in the same or different workbook.
NOTE: If you specify a whole folder of Excel files (Input Files), you can concatenate/append the data into a single sheet (within a single output file). When specifying a whole folder of Excel files, the input file should use the wild card (i.e. C:\MyFolder\ExcelFiles\*.XLS).
HINT: To specify the location to begin copying the data to (without the needing figure out the destination range) simply specify a single cell (i.e. "B10"). All the copied data will be placed starting at this cell.
Follow these steps to copy a portion of a worksheet from one Excel workbook into the same or another workbook:
1. Select the special process “[101] (XLS) Copy specified data from sheet to the same or different XLS file” from the Special Process drop down list. The file format is automatically set as *.XLS.
2. Next select the input and output files. If an output file/folder does not exist, Convert XLS will create it for you. If the output file is the same as the input file and a new sheet name is specified, a new sheet will be created within the input file’s workbook. To do a whole folder use the wildcard format (i.e. d:\XLSFiles\*.XLS).
3. Click on the Click Here for Copy Sheet Details button to specify information about the input and output files.
a. For the Input sheet, specify the sheet you wish copied by name or index number and whether you want to copy values or formulas. Next specify whether you want all data copied or a specific range of cells only.
b. For the Output sheet specifying the same sheet name will copy the data onto that sheet. By specifying a different name, 'Convert XLS' will create a new worksheet in the designated workbook. Choose from among the four Select where to copy to options. Note that if the output sheet already exists it may be overwritten.
4. Click the Add button to add the task to the Conversion Task list. You may add multiple tasks to the Conversion Task list before initiating the conversion process by repeating Steps 1 - 4 as necessary.
5. Click the Convert button to execute the process.
'Conversion Jobs', consisting of one or more conversion tasks may be saved, restored, and edited. Once saved, they may also be specified and run from the command line or used in the built in scheduler.
Additional Details
Input Sheet: specifies the name or index (an integer 1, 2, 3 etc. specifying the position of the sheet) of the sheet to copy data from.
Copy Formula: is used to specify whether to copy formula or values from the worksheet. Use "TRUE" to copy formula, otherwise "FALSE" to copy values.
Copy Sheet Name: should be set to checked if you want the newly created copy to have the same name as the input sheet. The Copy Sheet Name option is visible only if you are creating a new workbook by specifying an output File that is different from the Input File. This is because a Workbook file (XLS) can only have uniquely named sheets. This parameter is typically only used when the Input Sheet specifies the sheet by an index number.
Copy From Specified Range should be selected if you need to specify exactly which cells to copy. If not selected, the 'Used Ranged' will be copied. Typical Excel range syntax is acceptable. For example "A1:B10", "J5:K20", “A2:B4, J10” etc. If this is blank, the entire used portion/range of the worksheet will be copied.
Output Sheet specifies the name or index of the sheet to copy data to.
Copy To Same Range as the Input Worksheet should be chosen if you want to place the copied data in the same cells it resides in the Input Sheet.
Copy To Specified Range should be set selected if you want to specify exactly where the data will be placed. Specifying a single cell (i.e. "B10") will cause the data to be placed beginning at that cell. If you specify a smaller range than the copied range it will be truncated. Specifying a larger range than the copied range causes invalid data to be copied to the output sheet. Typical Excel syntax is acceptable. For example "A1:B10", "J5:K20", etc. If this parameter is blank, it is placed beginning at cell A1.
Copy Below Last Used Cell can be used to append/concatenate data to the last used row.
Copy to the Right of Last Used Cell can be used to append/concatenate data to the last used column.