QTP tutorials & interview questions

Search more on QTP here

How to convert from CSV to Excel and preserve format?

How to convert from CSV to Excel and preserve format?

In the below script I am preserving the format for columns B and F.

Option Explicit
Const vbNormal = 1

DIM objXL, objWb, objR ' Excel object variables
DIM Title, Text, tmp, i, j, file, name, savename

file = "new20.csv"
name = "new20"

savename = "c:\new20.xls"

Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function

' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window

' Load the Excel file from the script's folder
Set objWb = objXl.WorkBooks.Open(GetPath+file)

' Get the loaded worksheet object
Set objWb = objXL.ActiveWorkBook.WorkSheets("new20")
objWb.Activate ' not absolutely necessary (for CSV)

'WScript.Echo "worksheet imported"

' turn of those annoying warning messages

'wscript.echo savename

' xlWorkbookNormal
objxl.ActiveWorkbook.SaveAs savename, &HFFFFEFD1


Set objXL = Nothing
Set objWB = Nothing
Set objR = Nothing