Preserve file format from csv to xls

[malaygal]

I have this SCRIPT to open a csv file and save it as an excel file.

srccsvfile = "c:\Test.csv"
srcxlsfile = "c:\Test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.displayalerts=false

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)
objWorksheet1.SaveAs srcxlsfile, 2

How can I preserve the format of the csv file (i.e leading zeroes, number of decimal places)

Any Help will be greatly appreciated.

 

[BillDoor]

Unfortunately Excel does strip leading zeros from csv files. You can force Excel to import the field as text and preserve the leading zero, by surrounding the field with double quotes and prefixing it with an = sign, so:

John Smith,0115123456,Beer Brewer

becomes

John Smith,="0115123456",Beer Brewer

When imported into Excel the phone number is preserved.

Check this out for more info

http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel

====================
Mr Shine him diamond

 

[ShadowFox333]

Hi Malayagal,
Below is a script I use to do what you want.
On lines 42 and 43 the .numberformat= does what you want.
Line 43 c:c is a zip code that does not drop the leading
zeroes on New England states.

Enjoy Bob



Option Explicit
Const vbNormal = 1

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

file = "IBRENTAL.csv"
name = "IBRENTAL"

savename = "c:\rjm\IBRENTAL.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("IBRENTAL")
objWb.Activate ' not absolutely necessary (for CSV)

'WScript.Echo "worksheet imported"


objxl.columns("B:B").numberformat="0000000"
objxl.columns("C:C").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