Thursday, June 3, 2010

Excel COM - 4- Convert .csv to .xls

' CSV to XLS conversion
'Reference HP-Merucury QTP KB 10941 , for conversion of any delimited file to other.

In the code below,
Change the second and third lines to point the variables to the actual folders you want
Save this code in a file with .vbs extension
Execute this vbs file at a suitable point in your code when you need the xls file instead of a csv file.

Dim folderWithCsvFiles, folderExcelFilesGoInto
folderWithCsvFiles = "C:\myDataFolder\"
folderExcelFilesGoInto = "C:\"

Set appExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderWithCsvFiles)
Set fc = f.Files
For Each f1 in fc
fileName =
loc = Instr(1, fileName, ".csv", 1)
If loc > 0 Then
appExcel.Workbooks.Open(folderWithCsvFiles & fileName)
nameLength = Len(fileName)
newFileName = Mid(fileName, 1, (loc-1)) & ".xls"
appExcel.ActiveWorkbook.SaveAs folderExcelFilesGoInto & newFileName, 43 ' 43 is the code for xls format
End If
Set appExcel = Nothing
Msgbox("Done converting the files") ' you omit this line if you are not manually checking that this code is executed

1 comment:

Anonymous said...

Genial dispatch and this mail helped me alot in my college assignement. Say thank you you as your information.