Merge Excel Worksheets with 'Convert XLS'

Batch Excel merging of worksheets (whole or specific portions)


Faster Software

'Convert XLS' allows for the saving and restoring of complex 'conversion jobs'. Specify a 'conversion job' within the command line or Windows scheduler and take a break. 

'Merging Excel Files' — BATCH — 80x25
C:\>Working demo.bat files
   are installed for quick
   testing and modification.

C:\>TestCommandLine.bat

C:\>See detailed examples of
merging Excel worksheets by command line
at bottom of this page

Powerful Command Line

Power users: Command line capability is fundamental to ALL of our software tools. Example batch files are installed ready for testing and modification. We are always here to help you implement our software.

Easy to use
File Manipulation Features

Moving or deleting files while merging xls files is not a problem. This makes unattended conversion of whole folders easy to do.

'Convert XLS' Software for merging Excel worksheets

'Convert XLS' Features


If you need to manipulate CSV, XLS, XLSX, TXT file types, 'Convert XLS' comes with many features and special processes including:

General

Conversion Tool Extras
  • Convert up to 10-20 times faster without using Excel.
  • Automate 'Convert XLS' using the command line or the COM/ActiveX interfaces.
  • Move or delete files after processing. Skip files already processed.
  • Save and restore complex conversion configurations.
  • Batch can be stopped midway and resumed at a later time.
  • Special processing of files including appending/concatenation, removing extra lines, search and replace strings and much more.
  • Can be adapted for all languages.
  • Launch (Run) a third party program

Excel File Manipulation

Conversion and Manipulation of Excel Files

Excel File Conversion

Excel Conversion Features
  • Specify:
    • Sheets to convert
    • Create a single or multiple files
    • Skip empty worksheets
    • Range to convert (e.g. "B4:J10")
    • Use double quotes
  • Convert Excel files to and from Text/CSV/HTML/XML without needing MS Excel to be installed.
  • XLSX Conversion supported. Convert CSV to XLS, XLSX, TXT, HTML, XML etc.
  • In most cases it is possible to retain leading zeroes (e.g. 0000123213012)

More info on specific Excel file conversions

Our Customers


ibm customer

Great customer service, prompt attention to our requirements and lightening speed development has been my experience with the staff at Softinterface Inc. Within a few hours of installing...
Bruce King, IBM Canada, Toronto, Ontario



"Thanks. The product is terrific. It did exactly what I needed it to do. I had 1321 text files I needed to convert to Word. It did it in about 6 minutes."
Don Geiger Cullen and Dykman

adobe software customer

Adobe Systems Inc. Chooses 'Convert Doc'.  A custom site license has been signed by Adobe allowing them to install 'Convert Doc' on their servers.  They will be using it specifically to do Doc to HTML conversions. "We look forward to using your product and potentially working together in the future", Bob Free, Adobe


Your products are very impressive, easy to use and script compatible, for what we desired in the management of MS Word and Excel files. Thank you for your continue contact with me in regards to these tools.  Mark Purinton EMC Corporation


"Your wonderful utility took a mere ten minutes to append all 22,000 files into one gigantic 2 gigabyte file. For comparison purposes, the program that wrote the files took approximately 30 hours to write out said 22,000 files. System: 1GHz Pentium/512 Meg RAM Laptop"  'Convert XLS' 

235000

'Convert XLS' Downloads. Time tested (~20 years) for your demanding requirements.

54

Non-profit organizations assisted. Are you a member of one? Let us know, we would like to help.

110

Customers in 110 countries. 1 in 3 Fortune 500 companies use our software.
'Convert XLS' is compatible with Office 97 through Office 2016. Yes, we've been at it that long!

Special Promotion: Buy 'Convert XLS' and get 'Text to Excel Wizard', 'Convert Doc', 'Convert Image' and 'Convert PowerPoint' free!

Merging Excel Worksheets?

Using 'Convert XLS' to Merge Excel Worksheet Data


So you need to merge Excel worksheets (either a portion or a whole worksheet)?  If so we've built your tool!  'Convert XLS' can easily combine one or more worksheets/workbooks in addition to doing many other spreadsheet manipulations.  Below we detail how to use our tool both by the user interface and by command line. First we'll combine whole sheets, and then show how to merge specific portions. See the bottom of this page for some examples of merging of Excel worksheets from the command line.


Automate the combining of Excel sheets using:

You can even move/delete the input files after conversion. Skipping files already processed is another popular feature that is included.

Complete documentation is available on-line for review. Of course please do download it and try it for free.

Merge Excel Files (Whole Worksheet)

This special process can merge one or more worksheets you specify to the same or different workbook.

From within the user interface, follow these steps to merge an entire worksheet into the same or new Excel workbook:

1)     Select the special process "[102] (*.XLS) Copy entire sheet to the same or new XLS file" from the Special Process drop down list. The file format is automatically set as *.XLS.

2)    Next select input and output file paths. Output file may be the same workbook as the input. Or, if an output file does not exist, Convert XLS will create it for you.

3)    Click the ‘Click Here for Copy Sheet Details’ button.

merge excel worksheets dialog box

a.     Select original sheet to be copied/merged and the desired location in the output workbook.

b.     If you are copying a single sheet, you will have the option to give the new sheet a name different from the original ("Rename Sheet To…"). This option is only available for single sheet copies and will not be visible if you specify more than one sheet. Multiple sheet copies will rename the sheet based on your selection under "Action for duplicate sheet name(s)".

c.     Convert XLS can automatically rename or overwrite the newly created sheet(s), it can skip duplicate named sheets or halt execution and report in an error log that a duplicate-named page exists.

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.

 

Additional Details

Original Sheet Specifies the name or index of the sheet to copy.

Note: If you have named a sheet as a numeric (i.e. "1234"), you will need to use quote marks around that name. For example, if you name a sheet "32", when you specify the Original Sheet you must input "32" with the double quotes around the name so Convert XLS knows it is a name and not an index number for the sheet. If you browse for the file and click to select it, you will still need to manually add the quotes.

Target Sheet specifies where the new copy will be made: FIRST sheet in workbook; LAST sheet in workbook; or browse to the sheet you'd like the copies placed BEFORE or AFTER.

Action For Duplicate Sheet Names: Specification for how to handle duplicate sheet names. A duplicate exists if the Original Sheet already exists in the output workbook. If a duplicate exists you can:

A)   Copy sheet but have Excel give the new sheet a slightly different name

B)    Overwrite existing sheet

C)    Skip this sheet and don't copy. Goes on to the next sheet if more than one sheet was specified

D)   Stop processing if a duplicate sheet is found

Rename Sheet To lets you give the new sheet a name that differs from the original; this is only available for single sheet copies.

Action for Duplicate Sheet Names lets you specify how to name sheets when more than one is being copied at a time. COPY renames sheets with a similar name by appending a number to it, e.g. Monthly(2); OVERWRITE will overwrite the data in an output sheet that's named the same as the original sheet, and then it will move it to the location you specified in the output Sheet option; SKIP lets you skip any duplicate named sheets; and, STOP will halt execution of the copy job and report an error in the log.


Merging a Portion of a Worksheet (aka Combine/Append/Concatenate)


This special process can merge 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 combine/concatenate/append the data into a single sheet (Output File). When specifying a whole folder of Excel files, it should look like this: C:\MyFolder\ExcelFiles\*.XLS

HINT: To specify the location to begin merging 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 combine 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 input and output paths. If an output workbook does not exist, Convert XLS will create it for you. If the output is the same as the input file and a new sheet name is specified, a new sheet will be created. To do a whole folder use the wildcard format (i.e. d:\Folder\*.XLS).

3)    Click on the ‘Click Here for Copy Sheet Details’ button where you will add information about the input and output files.

merge xls files dialog box

a.     For the Original 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 Target 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 target sheet already exists it will 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.

Additional Details

Original Sheet: specifies the name or index 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 original. The Copy Sheet Name option is visible only if you are creating a new workbook by specifying an output 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 Original 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. If you use "," within the range to specify multiple ranges, each item will be copied as if you had specified a new conversion item for each.

Target Sheet specifies the name or index of the sheet to copy data to.

Copy To Same Range as the Original Worksheet should be chosen if you want to place the copied data in the same as it resides in the Original 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 target sheet. Normal Excel range 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.





Merge Excel Worksheets command line

NOTE: After installation, you can find simple batch files (e.g. TestCommandLine.bat) for quick and easy use by going to Start\All Programs\Convert XLS\Example Batch Files. These will work without modification provided the installation was in the default folder. Email us at  if you have any questions or need additional examples. We will get you up and running fast!

Although we'll discuss the use of the command line here, it's only to give you a taste. For the full, accurate, and up to date information see the 'Convert XLS' command line documentation. If you have trouble running these examples see Helpful Hints For Creating a Command Line.


There are two approaches to using the command line:

  • Specify one or more conversion tasks in a conversion job
  • Specify a single conversion task in detail

A 'Conversion Job' is easily built using the 'Convert XLS' graphical user interface. All the details of the job are saved in a single conversion job file (e.g. 'c:\myjobs\Do-Multi-Step-Combining.SII). Specifying a conversion job file with the command line is very easy by using the /J switch. You can have hundreds or thousands of tasks specified in the conversion job, all of which will get executed in one fell swoop. For example:

ConvertXLS.EXE /J"D:\Job Files\Combine Excel Sheets.SII"


Specifying a single conversion task requires a bit more effort, but does not require the prior creation of a 'Conversion Job' file.
The first example shows how to convert the most popular form of a CSV file, a comma delimited file, to an Excel file. The second example shows how to convert any character delimited (tab, bar, semicolon etc.).
Each switch shown in blue is described in detail at the bottom of this page. It is highly recommended you review each switches description before implementing the example, thereby giving you the full power of 'Convert XLS'.

Command Line Examples: Merge Entire Worksheet From the Command Line

The special process constant associated with copying a sheet is 102.

/P#

Meaning

Extra Parameters: /1{Parameter1} … /n{Nth Parameter}

102

Copy entire sheet to the same or new XLS file.
 

/1 = sOriginalSheet (by name or by number)

/2 = sSheetBefore (by name or by number)

/3 = sSheetAfter (by name or by number)

/4 = lActionOnDuplicate ()

/5 = sTargetSheetRename


This special process can copy one entire worksheet to the same or new workbook.

Special Parameters Described in Detail

/1{sOriginalSheet} Specifies the name or index of the sheet to copy.

/2{sSheetBefore} Copied sheet will be placed before this sheet (Name or #). If this parameter is equal to "firstfirst", then the new sheet is placed as the first sheet in the workbook.

/3{sSheetAfter} Copied sheet will be placed after this sheet (Name or #). If this parameter is equal to "lastlast", then the new sheet is placed as the last sheet in the workbook.

/4{lActionOnDuplicates} Specification for how to handle duplicate sheet names. A duplicate exists if the sOriginalSheet already exists in the output workbook. 0=Copy sheet but give a slightly different name automatically, 1=Overwrite existing, 2=Skip this sheet and don't copy, 3=Stop processing if a duplicate is found.

/5{sTargetSheetRename} If you are copying a single sheet, you can specify the name of the sheet using this parameter. Leave blank to not rename.

Examples:

EXAMPLE 1 Copy entire sheet, and place it at the end of the workbook

This example shows how to copy the "Sheet1" of the workbook "D:\Data Folder\B.XLS", to a new sheet named "Sheet1Renamed", placed as the last sheet within the same workbook. If a sheet by the same name of "Sheet1Renamed" exists, we'll copy anyway and have Excel give it a slightly different name. The following syntax would be used:

ConvertXLS.EXE /S"D:\Data Folder\B.XLS" /T"D:\Data Folder\B.XLS" /P102 /1"Sheet1" /3lastlast /40 /5"Sheet1Renamed"

Note: You don't have to specify Boolean (TRUE/FALSE) parameters that are FALSE. Nor do you have to specify parameters that are empty.

The /S and /T switches specify input and output workbook respectively, which in this case is the same workbook. You can optionally keep the input workbook unchanged by specifying different files for the input and output.

EXAMPLE 2 Copy entire worksheet from one workbook to a new workbook

To copy the "Sheet1" of the workbook "D:\My Data\B.XLS", to a target sheet named "CopyOfSheet1" and placed it in a new workbook "D:\My Data\C.XLS" in front of a sheet named "Summary", the following syntax would be used:

 ConvertXLS.EXE /S"D:\My Data\B.XLS" /T"D:\My Data\C.XLS" /P102 /1"Sheet1" /2Summary /40 /5"CopyOfSheet1"


Command Line Examples: Combine Specified Sheet Data to same or different XLS file

The special process constant associated with copying a sheet data is 101.

/P#

Meaning

Extra Parameters: /1{Parameter1} … /n{Nth Parameter}

101

(XLS) Copy specified data from sheet to the same or different XLS file. Can be used for appending/concatenating many sheets data to a single sheet
 

 /1 = sInputSheet (by name or by number)

 /2 = lWhatToCopy

        0 = Values (DEFAULT)

        1 = Formulas

        2 = Values and Number Formats

        3 = Formulas and Number Formats

        4 = All

        5 = Comments

        6 = Formats

        7 = Validation

        8 = All Except Borders

        9 = Column Widths

 /3 = bCopySheetName  ("TRUE" or "FALSE")

 /4 = bCopyFromSpecifiedRange  ("TRUE" or "FALSE")

 /5 = sSpecifiedRangeToCopyFrom

 /6 = sOutputSheet (by name or by number)

 /7 = bCopyToSpecifiedRange ("FALSE" or "TRUE")

 /8 = sSpecifiedRangeToCopyTo

 /9 = bCopyColumnWidths ("FALSE" or "TRUE")

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, you can concatenate/append the data into a single sheet.

Special Parameters Described in Detail

Note: You don't have to specify Boolean (TRUE/FALSE) parameters that are FALSE.  Nor do you have to specify parameters that are empty.

/1{sInputSheet} specifies the name or index of the sheet to copy data from.

/2{lWhatToCopy} is used to specify what to copy from the worksheet.  For example to copy Values use /2 0

If this switch is left out, the Values will be copied by default.

/3{bCopySheetName} should be set to "TRUE" if you want the newly created worksheet to have the same name as the input worksheet.  bCopySheetName can be true only if you are creating a new workbook by specifying an Output Target File (/T) that is different from the Input File (/S).  This is because a Workbook file (XLS) can only have uniquely named sheets.  This parameter is typically only used when sInputSheet specifies the sheet by an index number. Multiple input sheets are not possible when setting this parameter to TRUE.

/4{bCopyFromSpecifiedRange} should be set to "TRUE" if you plan on using the sSpecifiedRangeToCopyFrom parameter, otherwise set it to "FALSE".  If set to "FALSE" the entire 'Used Ranged' will be copied.

/5{sSpecifiedRangeToCopyFrom} is used to select a portion of a sheet to copy the data from.  Typical Excel range syntax is acceptable.  For example "A1:B10", "J5:K20, Z20", etc.  If this parameter is blank, the entire used portion of the worksheet will be copied.

/6{sTargetSheet} specifies the name or index of the sheet to copy data to.

/7{bCopyToSpecifiedRange} should be set to "TRUE" if you plan on using the sSpecifiedRangeToCopyTo parameter, otherwise set it to "FALSE".  If set to "FALSE", the data will be placed beginning at cell A1.

/8{sSpecifiedRangeToCopyTo} is used to select exactly where to place the copy data to within the output worksheet, sTargetSheet.  Normal Excel range expressions are acceptable.  For example "A1:B10", "J5:K20", etc.  If this parameter is blank, it is placed beginning at cell A1.   If the range specified is smaller than the copied data's range, the data will be truncated.  To copy the data below already existing data, set this parameter to "below".  To copy the data to the right of existing data, set this parameter to "right".

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.

/9{bCopyColumnWidths} In addition to copying values, formulas, formats, comments etc., you can also, at the same time specify to copy the column widths. Use /9TRUE to copy column widths, /9FALSE to not. If this switch is left out, it defaults to FALSE.

 

EXAMPLE 1 Copy all data from one sheet to another, in same workbook

To copy the entire used range of  Values of "Sheet1" of the workbook "D:\XLSFilesData\A.XLS", to "Sheet2" of the same workbook, the following syntax would be used:

ConvertXLS.EXE /S"D:\XLSFilesData\A.XLS" /T"D:\XLSFilesData\A.XLS" /P101 /1"Sheet1" /6"Sheet2"

The /S and /T switches specify input and output workbook respectively, which in this case is the same workbook.  You can optionally keep the input file unchanged by specifying a different name or path for the output file.

EXAMPLE 2 Copy specified range of formula from one workbook to another

To copy a specific range (B5:K10) of Formulas of "Sheet2" of the workbook "D:\XLSFilesInfo\A.XLS", to "Sheet3" (placing the contents beginning at cell J10) of the workbook "D:\XLSFilesInfo\B.XLS", the following syntax would be used:

ConvertXLS.EXE /S"D:\XLSFilesInfo\A.XLS" /T"D:\XLSFilesInfo\B.XLS" /P101 /1"Sheet2" /2 1 /4TRUE /5B5:K10 /6"Sheet3" /7TRUE /8J10 

EXAMPLE 3 Append sheet data from a folder of workbooks to a single sheet

To copy the used range of Formulas of "Sheet1" of all the workbooks in the "D:\ABC\" folder, to "Sheet1" (placing the appended data below existing data) of the workbook "D:\Output\Target.XLS", the following syntax would be used:

ConvertXLS.EXE /S"D:\ABC\*.XLS" /T"D:\Output\Target.XLS" /P101 /1"Sheet1" /2 1 /6"Sheet1" /7TRUE /8below

To place the data to the right of existing data simply change /8below to /8right

Note it is highly encouraged that you use the Verbose (/V) switch initially to see what the status of your conversion is.

EXAMPLE 4 Copy ALL including column widths from one workbook to another.

To copy everything in the entire used range "Sheet1" of the workbook "D:\XLSFiles\A.XLS" including column widths, to "Sheet2" of workbook “D:\XLSFiles\B.XLS”, the following syntax would be used:

ConvertXLS.EXE /S"D:\XLSFiles\A.XLS" /T"D:\XLSFiles\B.XLS" /P101 /1"Sheet1"/2 4 /6"Sheet2" /9TRUE

The /S and /T switches specify input and output workbook respectively. The /9 switch specifies to copy the column widths, while the /2 switch specifies to copy ALL.