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"


objxl.columns("B:B").numberformat="0000"
objxl.columns("F:F").numberformat="00000"
' turn of those annoying warning messages
OBJXL.DISPLAYALERTS = fALSE

'wscript.echo savename

' xlWorkbookNormal
objxl.ActiveWorkbook.SaveAs savename, &HFFFFEFD1


objXl.Quit()

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

[Via]