If you must get started quickly use the following section to find an example and modify according to your needs:
Examples For Applying Special Processes
Special processes include copying data between sheets, manipulating sheets, applying filters to comma-delimited files (etc.) etc. For the complete list see the large table at the bottom of this topic.
The syntax for the command line for specifying a 'Special Processing Task' is:
ConvertXLS /P# /1{Parameter1} /2{Parameter2} /n{Nth Parameter}
/S{Input File(s)} /T{Output File(s)} [/F#] [/G] [/V] [/R] [/L{LogFile}] [/B{LogToJobFile}] [/W{PW}] [/X{PW}] [/Y{PW}] [/Z{PW}] [/H]
Items above enclosed in square brackets "[ ]" are optional, all other switches are required. Therefore /P#, /1 /n, /S, and /T should always be specified on the command line. There is one exception however, and that is when /G (save to input folder) is specified, the /T switch is not necessary.
Command line switches are case sensitive, therefore /S is valid and /s will not work.
You can include or exclude spaces between switches and parameters. For example, "/P101" is the same as "/P 101".
For long file names you may be required to use double quotes.
After reviewing this section, we highly recommend viewing the examples provided in later sections.
Switches:
/P# is used to specify the 'Special processing task' as a number. The table below outlines each possible value.
/1{Parameters} /n{Nth Parameter} Depending on the special process specified by /P#, additional information/parameters may be necessary. Again, the table below describes each special process, and any Extra Parameters that may be necessary.
/S{Input File(s)} is used to specify which files to use to apply the special processing on. You can specify a single file, or in some cases a whole set of files using the wildcard syntax (i.e. "C:\MyFiles\*.XLS"). If /T is different from /S, the input Source files will be untouched. If you want to have the input files modified by the special process, be sure to specify the same value in the /T{Output files}. Otherwise, you can leave the input files unchanged by specifying a different set of files for the /T {Output Files}.
/T{Output File(s)} is used to specify which files to save the processed data to. You can specify a single file, or in some cases a whole set of files using the wildcard syntax (i.e. "C:\MyFiles\*.XLS"). If /T is different from /S, the input Source files will be untouched. If you want to have the input Source files modified by the special process, be sure to specify the same value in the /T{Output files}. Otherwise, you can leave the input files unchanged by specifying a different set of files for the /T {Output Files}. Date/Time Fields are allowed.
[/F#] tells 'Convert XLS' what the file types are being processed. Only required for /P50 - /P75 (Append, Remove empty lines, Search and replace string, Replace character). For all constants see 'Special Process' File Type Constants.
[/G] Save files to their input folders. Use this instead of /T to place the converted files in the same folder as the input file. Cannot be used when converting a single, specified file from within a folder. Use the /T switch instead.
[/V] Verbose mode. Specify this switch to display a message box indicating how the conversion went. See also /L
[/R] Seek out and do all files found in the sub-folders specified in the /S switch. Recursive subdirectories. You may add this switch if you are processing whole directories of files. See /S, /T and /G for more details on selecting whole directories to be converted.
[/L{LogFile}] Log file path and name (i.e. d:\mylogs\WCE.LOG) If this switch is specified a log file with the given path and name will be created and the results of the conversion will be written to it. See also /V
[/B{LogToJobFile}] Save the Log file as a 'Conversion Job' ONLY IF ERRORS OCCURRED. This is useful if the files that had problems, can be done at a later time, or with a different method. /B is different from /L, since the file generated with /B will not include extraneous comments not allowed in a 'Conversion Job' file. Save as a .SII file type so it can be loaded as a job.
[/W{PasswordToOpenInputFile(s)}] Password to open the input file.
[/X{PasswordToWriteToInputFile(s)}] Password to allow for the writing to the input file.
[/Y{PasswordToOpenOutputFile(s)}] Password to open the output file.
[/Z{ PasswordToWriteToOutputFile (s)}] Password to allow for the writing to the output file.
[/H] If converting from XLS to something else and using the 'Convert XLS' conversion method (/M2) you can optionally specify to include values along with formula, much like "MS Excel" conversion method does.
Note: For the /1{Parameter1} /n{Nth Parameter} items, you don't have to specify Boolean (True/False) parameters that are FALSE. Nor do you have to specify parameters that are blank.
Please see the Special Processing Examples section for full examples, and detail of each parameter.
Click on the links in the table below to jump to an example of usage and full parameter detail.
/P# |
Meaning |
Extra Parameters: /1{Parameter1} /n{Nth Parameter} |
/1 = sInputSheet (by name or by number) /2 = lWhatToCopy 0 = Values 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") |
||
/1 = sInputSheet (by name or by number) /2 = sSheetBefore (by name or by number) /3 = sSheetAfter (by name or by number) /4 = lActionOnDuplicate () /5 = sOutputSheet (by name or number) |
||
/1 = sSheet Start (by name or by number) /2 = sSheetEnd (by name or by number) /3 = sSheetDeleteAllExceptThisOne (by name or by number) |
||
/1 = sSheetToAdd (by name) /2 = sSheetBefore (by name or by number) /3 = sSheetAfter (by name or by number) /4 = bOverwriteExisting ("TRUE" or "FALSE") |
||
/1 = sSheetToMove (by name or by number) /2 = sSheetBefore (by name or by number) /3 = sSheetAfter (by name or by number) |
||
/1 = sSheetToRename (by name or by number) /2 = sNewSheetName (by name or by number) |
||
/1 = sSheetStart (by name or number) /2 = sToDelete (Numeric) /3 = bDeleteColumns ("TRUE" or "FALSE") |
||
/1 = sSheetStart (by name or number) /2 = sRange /3 = sNewFormat /4 = lNewFormat |
||
/1 = sSheet - worksheet to be modified (by name or number) /2 = sSearchFor - text to be modified /3 = sReplaceWith - replacement text /4 = sRange - Optionally specify a portion of the sheet /5 = bCaseSensitive - "TRUE" or "FALSE" /6 = bLookAtPart - "TRUE" or "FALSE" /7 = bMatchByte - "TRUE" or "FALSE" |
||
(XLS) Transpose Sheet. Converts Rows to Columns and Columns to Rows |
/1 = sSheet - worksheet to transpose |
|
/1 = Password to open the Input file /2 = Password to write to the Input file /3 = Password to open the Output file /4 = Password to write to the Output file See /W-/Z Also |
||
/1 = lWhatToClear 0 = Clear ALL 1 = Clear FORMATS 2 = Clear CONTENTS 3 = Clear COMMENTS 4 = Clear NOTES 5 = Clear OUTLINE /2 = sSheetStart (by name or number) /3 = sRange (leave blank for 'used range')
|
||
/1 = sPrinter (not sure what this is, use the user interface to see strings associated with printers) /2 = sSheetsToPrint (by name or number) /3 = sRange (leave blank for 'used range') /4 = lNumber of copies /5 = lPageFrom (leave empty to start printing from the beginning) /6 = lPageTo (leave empty to terminate printing at the end) /7 = bCollate ("TRUE" or "FALSE"). Set to "TRUE" to collate multiple copies /8 = bPrintToFile ("TRUE" or "FALSE"). Set to true if printing to a file. You must specify an output file if printing to a file. |
||
/1 = sMacroName /2 = bSaveAfterRunning ("TRUE" or "FALSE"). Set to "TRUE" to save the workbook after running the macro. You must specify the /T command line argument if you set this to true. /3 = sArg1 Use the next 8 parameters (/3-/10) if and only if your macro requires arguments. /4 = sArg2 /5 = sArg3 /6 = sArg4 /7 = sArg5 /8 = sArg6 /9 = sArg7 /10 = sArg8 |
||
Import tables from doc, html, xml, rtf, wpd etc into an Excel worksheet(s)/workbook(s) |
/1 = sTablesToImport (i.e. "1,3-5"). To import all tables use the asterisk "*" character (i.e. "/1*" ). /2 = lTableImportMethod: Valid values (0,1,2) There are 3 methods to import the table into MS Excel. First choice, 0, is to import to a specified sheet. All tables specified will be copied to the same sheet with this option. Second choice, 1, is to create a new sheet for each table chosen to be imported. The names of the sheets will be based on the "Output Sheet Name or Number" specified. The last option, 2, allows you to create a new workbook file for each table specified. The output file name in this case is based on the input file and table numbers specified. /3 = sSheetToImportTo Specify the name or number of the sheet to import the tables to. /4 = sPasteToCell Specify the starting cell to begin pasting the tables into (i.e. B3) /5 = lRowsBetweenTables Row Offset between each table: /5 and /6 are only relevant for when you've specified to copy more than one table into a single sheet (e.g. /2 0). To place each table 2 rows below the last tables data, specify "/5 2" and don't use /6. To place each table 3 columns to the right of each other don't use /5 and specify "/6 3". Specify a value of 0 for /5 or /6 to make the data contiguous (no spacing between tables). /6 = lColsBetweenTables Column offset between each table. See /5 for details. /7 = sRowsToDelete Remove Rows: You may not want the first row (very common) of each table being imported. So you can easily specify any combination of rows or columns you want removed from the table when being imported. To remove rows 1, 4, 5 and 6 specify "/7 1,4-6" for example. To remove the 2nd column use "/8 2" /8 = sColsToDelete See /7 for an explanation. /9 = bRemoveFormatting Set to TRUE to only copy table data: Here you may only copy the textual content of the table and ignore any formatting the input file had. Set to FALSE to retain formatting etc. |
|
/1 = sInputSheet (by name or by number) /2 = sRange (Specify which cells to change. (leave blank for 'used range') /3 = sNewContent (what is it you want to change the cell content to) /4 = bChangeFormula ("TRUE" to modify the Formula, or "FALSE" to modify the Value) |
||
NONE |
||
/P# |
Meaning |
Extra Parameters: /1{Parameter1} /n{Nth Parameter} |
NONE |
||
NONE |
||
/2 = Decimal ASCII numeric value of the character to replace the "," comma with. For example to swap out the comma with a pound symbol "#" specify: /2 35 |
||
NONE |
||
/1 = Selection of rows to include. All others will be discarded.
For example to keep rows 4-20, 25, and 30 specify: /1 4-20,25,30 |
||
/1 = Selection of columns to include. All others will be discarded.
For example to keep columns 4-20, 25, and 30 specify: /1 4-20,25,30 |
||
NONE |
||
NONE |
||
/1 = lLength represents length of field(s) with padding /2 = sPadChar is the ASCII numerical value of the padding character /3 = bPadLeft ("TRUE" or "FALSE") Set to "TRUE" to pad left /4 = lColumn indicates the column(s) to be padded |
||
/P# |
Meaning |
Extra Parameters: /1{Parameter1} /n{Nth Parameter} |
NONE
Must specify /F# with the Special Process File Type Constants |
||
NONE
Must specify /F# with the Special Process File Type Constants |
||
/1 = sTextToFind /2 = TextToReplace found text with /3 = bCaseSensitive ("TRUE" or "FALSE"). Set to "TRUE" for case sensitivity. Must specify /F# with the Special Process File Type Constants |
||
/1 = sSearchFor /2 = sReplace Must specify /F# with the Special Process File Type Constants |
See also:
Helpful Hints for Creating a Command Line
Specifying a 'Conversion Job' File (Easiest way to do command line)
Specifying a 'Conversion Task'
Specifying a 'Special Processing' Task
Special Process File Type Constants