ExportListToExcel

×V4.1Ø

Syntax:

ExportListToExcel  <List Name> <FileNameDestination> [<SheetNameToExportTo>] [<ExportMode>][<MaintainCellType>]

       Where:

               <List Name> is the user defined name that is assigned to the list.

               <FileNameDestination> is a literal string or a variable that contains a literal string.

               <SheetNameToExportTo> is a literal string or a variable that contains a literal string.

               <ExportMode> is a row number or one of the following keywords or a variable that contains a row number or one of these keywords:

                               APPEND, OVERWRITE, DONOTOVERWRITE, ROWNUMBER.

               <MaintainCellType> is a keyword.

Description:

This command is used to take the contents of a List and export them to the specified Excel file.  The FileNameDestination parameter defines the directory and file name, including extension, where the list will be exported.  SheetNameToExportTo is an optional parameter that if not specified will default to Sheet1.  If Sheet1 exists and has data it will not over write and cause an error.  ExportMode has 4 modes: APPEND, OVERWRITE, DONOTOVERWRITE, or ROWNUMBER.  APPEND will add the contents of the list to the existing data in the sheet specified.  OVERWRITE will remove any existing data and replace it with data from the list.  DONOTOVERWRITE is the default option for this command and will write the list to the sheet only if there is no existing data.  ROWNUMBER is an integer, literal string or a variable that contains an integer.  This allows the list to be added starting at the specific row number given.  If you specify a row number any data existing in that row number or after will be overwritten for the size of the list being exported.

When the optional parameter MaintainCellType is used,  EMUE will convert string values to the data type that is specified by Excel's formatting for that cell. EMUE does the following in order before each cell is written:

1) Checks target cell to see if it is a percentage, then checks data to see if last character is a %. If the last character is a % then the rest of the data is numeric it formats data as a number and divides by 100.

2) Checks to see if the data has a leading zeros. If the data is numeric it is formatted as an integer and preserves the leading zeros.

3) Checks to see if the data is numeric then changes it to a decimal.

4) Checks to see if data is a date then changes it to a date.

5) All other data remains a string.

For example, if a value of 10.5 is being exported to a cell that has type currency, then the value will be converted to $10.50.

Note: If #INPUTFILENAME# is used and there is no input file specified, the text will not be replaced.  The following key terms may be used in the name of the file for export.  These terms will be valued when the command runs and the values will be used in the name of the file:

If the Excel file specified does not exist, EMUE will create it.

Note: If using a sheet name moniker i.e. "*WorkSheet1" and the sheet does not exist, the list will not be exported and EMUE will produce an error.

Sample 1:

       The following example will export List_01 to the 2003 excel file using the default sheet name (Sheet1) & default export mode (DoNotOverWrite).

ExportListToExcel List_01 "C:\EMUE\Excel_Files\2003_Excel_File.xls"

Sample 2:

       The following example will export List_02 to the 2007 excel file using the default sheet name (Sheet1) & export mode (OverWrite).

ExportListToExcel List_02 "C:\EMUE\Excel_Files\2007_Excel_File.xlsx" "" OverWrite

Sample 3:

       The following example will export List_03 to the 2010 excel file using the given sheet name (PatientData) & export mode (RowNumber) which will start at row 2 allowing the headers to stay.

ExportListToExcel List_03 "C:\EMUE\Excel_Files\2010_Excel_File.xlsx" "PatientData" 2

Sample 4:

       The following example will export List_04 to the 2010 excel file using the work sheet moniker name (*WorkSheet1) & export mode (Append) allowing the data in the list to be added to the data in the excel file.

ExportListToExcel List_04 "C:\EMUE\Excel_Files\2007_Excel_File.xlsx" "*WorkSheet1" Append

Sample 5:

       The following example will export List_04 to the 2010 excel file using the work sheet moniker name (*WorkSheet1), export mode (Overwrite) & MaintainCellType so the cell format is preserved in the target worksheet.

ExportListToExcel List_04 "C:\EMUE\Excel_Files\2007_Excel_File.xlsx" "*WorkSheet1" OverWrite MaintainCellType

See Also:

CreateList

DisposeList

ListExists

FindListPosition

GetListCount

GetListMember

GetListPosition

PutListMember