FYI: We're upgrading our website look and feel over the next 30 days. This page will be done soon!

Copy Worksheet data to/from the same or different workbook easily with 'Convert XLS'.


Customers
 

Copy worksheet software, adobe 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

Copy Sheet 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


Click for the BBB Business Review of this Computers Hardware, Software & Services in Santa Monica CA

software, no viruses


You need to convert from

XLSX to CSV (Unicode/ASCII/ANSI)

XLSX  to Fixed Width Text (Unicode/ASCII/ANSI)

XLS to CSV (Unicode/ASCII/ANSI)

XLS to Fixed Width Text (Unicode/ASCII/ANSI)

CSV to XLS

CSV to XLSX

CSV to Fixed Width Text (ASCII/ANSI/Unicode)

Fixed Width Text File to XLS

Fixed Width Text File to XLSX

Fixed Width Text File to CSV

More... 

Copy Excel Worksheet Data with 'Convert XLS'

(MS Excel Batch Copy of whole or portions of worksheets)

English, Deutsch, Franšais, Italiano, 日本語, Brazilian Portuguese, Espa˝ol, Other Languages

'Convert XLS' logo, can be used to copy sheets between workbooks in a automated way.

Do you have a requirement to copy worksheet(s) in their entirety from one or more Excel files to a single workbook (or many) in an automated fashion?  Or perhaps your job is to copy a specific range of data from one or more workbooks/worksheets to a single (or many) worksheet?  Whatever the case we've got your tool.  Softinterface has created a sophisticated software utility called 'Convert XLS' which has many 'special processes' to convert/manipulate Excel files (including the copying of Excel worksheet data).  Read below for details on this specific feature, or click on the 'Convert XLS' home page to get a better idea of what the product does overall.

See the bottom of this page for some examples of how to automate copying of Excel worksheets. We have a built in scheduler as well as a command line (and a COM/ActiveX) interface which will allow you to leverage 'Convert XLS' to automate your workplace!

'Convert XLS' video, showing how to copy worksheet files

Online Training Videos

Copy Data From Multiple Workbooks to a Target Workbook that contains formulas and formatting

Quick Start: How to Convert Files

Download 'Convert XLS' a tool which can be used to copy sheets between workbooks

Office 2013 Compatible!

 
 

Copying an Entire Worksheet

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

Follow these steps to copy 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 original and target files. Target file may be the same workbook as the original file. Or, if a target file does not exist, Convert XLS will create it for you.

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

copy worksheet dialog box

a.     Select original sheet to be copied and the desired location in the target 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 named sheets. A duplicate exists if the Original Sheet already exists in the target file. 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 a target sheet that's named the same as the original sheet, and then it will move it to the location you specified in the Target 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.

Copying a Portion of a Worksheet. (Append/Concatenate)

This special process can copy the data (values,formula, comments, formatting etc.) you specify from one worksheet to another in the same or different workbook.

NOTE: If you specify a whole folder of Excel files (Original Files), you can concatenate/append the data into a single sheet (Target File). When specifying a whole folder of Excel files, it should be specified like this: C:\MyFolder\ExcelFiles\*.XLS

HINT: To specify the location to begin copying the data to (without needing to 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.

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 where you will add information about the input and output files.

copy worksheet portion dialog box

a.     For the Input sheet, specify the sheet you wish copied by name or index number and what you want to copy (values, formulas, formatting, notes, column widths, all etc.).  Next specify whether you want all data copied or a specific range of cells only.

b.     For the Output sheet specify the sheet you wish to copy to. If the sheet specified does not exist '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 data 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 (or F5) 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

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.

What to copy can be values, formulas, formatting, notes, all, column widths etc.

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 Column Widths is used to copy the widths of the input sheet.

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.

 

Command Line Examples: Copying Data from Excel Worksheet

NOTE: 'Convert XLS' can be used simply with the graphical user interface (GUI).  If you've never heard of 'Command Line' before, we would recommend not reading this section and instead download and try the software.

This is just a sample from the documentation.   If you have trouble running these examples please see Helpful Hints For Creating a Command Line within the documentation.

Command Line Explained

‘Convert XLS’ can be run without a user interface with command line arguments much like in the good old DOS days.  Alternatively you can initiate command line execution by going to the operating system 'command prompt' or within Windows at the START-RUN menu and typing it in there.  Lastly, the command line interface can be used from Batch files (files with *.BAT extension). 

There are two approaches to using the command line:

     A)  Specify a 'Conversion Job' that was built using the graphical user interface (GUI); or

     B)   Specify a 'Conversion Task' in detail from the command line.

See the  'Convert Xls' documentation for a full explanation and reference.  Below we focus specifically on using 'Convert XLS' to execute an Excel Macro from the command line.

Command Line Examples: Copy 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 named sheets. A duplicate exists if the sOriginalSheet already exists in the target file. 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:\XLSFiles\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:\XLSFiles\B.XLS" /T"D:\XLSFiles\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 original and target workbook respectively, which in this case is the same workbook. You can optionally keep the original file unchanged by specifying different files for the original and target.

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

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

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

Command Line Examples: Copy 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 ("TRUE" or "FALSE")

 /8 = sSpecifiedRangeToCopyTo

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

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 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.  Typical Excel syntax is 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:\XLSFiles\A.XLS", to "Sheet2" of the same workbook, the following syntax would be used:

ConvertXLS.EXE /S"D:\XLSFiles\A.XLS" /T"D:\XLSFiles\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:\XLSFiles\A.XLS", to "Sheet3" (placing the contents beginning at cell J10) of the 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"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:\XLSFiles\" folder, to "Sheet1" (placing the appended data below existing data) of the workbook "D:\XLSFiles\Target.XLS", the following syntax would be used:

ConvertXLS.EXE /S"D:\XLSFiles\*.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.

 

See Also: