Convert CSV file to Excel Using Pre-Script

This topic describes how to convert the extracted CSV file into Excel, using the pre-script functionality that converts the CSV file generated by Process Runner to a formatted native Excel file.

For more information about pre-run and post-run script feature, see Pre/Post Script.

To convert CSV file to Excel using pre-script,

1. Open a Process file whose data must be converted into an Excel file, select Edit > Pre Script. The Pre-Run/Post-Run Process Settings window is displayed.

   

2. On the Pre-Run iScript tab, select Pre-Run Process, Internal Script from the dropdown list, and then VB Script.
3. In the Process Settings section, select one of the following options:
   • 1-No wait: Indicates that the pre-script run does not wait for external process.
   • 2-Wait Until Terminated: Indicates that the pre-script run waits for the external process to get terminated.
   • 3-Wait Until Second: Indicates that the pre-script run waits for the number of seconds that you have entered in the Seconds box.
   • 4-Stop on error: Indicates that the external process stops when error is occurred.
4. In the Internal Script editor, remove the existing script and paste the following code.

   Script

   Dim strFileName

   Dim strFileNameXL

   Dim FSObj

   Dim wBook                '==== Excel workbook object

   Dim wSheet

   Const xlDelimited  = 1

   Dim wsBook

   Dim wsSheet

   Dim objExcel

   strFileName ="C:\YourFile.CSV"

   strFileNameXL ="C:\YourFile.xls" '========= Keep a blank file with this name that you will use as an external Excel file

   set FSObj = CreateObject("Scripting.FileSystemObject")

   If FSObj.FileExists(strFileNameXL) Then

       Set wsBook = GetObject(strFileNameXL)    

       Set wsSheet = wsBook.Worksheets(1)

   End If

   Set objExcel = CreateObject("Excel.Application")

   objExcel.Visible = True

   ObjExcel.Workbooks.OpenText strFileName,,,xlDelimited,,,,,,,True,","   '======= Here is is assumed that fields are separated by comma. If you want to use any other character as field separator, put that character here in place of comma

   ObjExcel.ActiveSheet.Cells.Select                

   ObjExcel.Selection.Copy

   wsBook.Application.Visible = True      

   wsBook.Windows(wsBook.Name).Visible = True

   wsBook.Activate

   wsSheet.Cells("1","A").Select

   wsSheet.Paste

   ObjExcel.CutCopyMode = False

   ObjExcel.ActiveWorkbook.Close false

   ObjExcel.Quit

5. Select OK.
6. Save the Process file and select Run. The comma-separated text file is converted to Excel file in the specified path.