Recently I wanted to convert a CSV file to an XLS spreadsheet using a script rather than opening the file in Excel, then re-saving as an XSL file.

To solve this task, I used the Microsoft Excel COM Object. Below are the properties of the method (Open) used to open a workbook:-

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

I am interested in the properties FileName, Format and Delimiter.

[Format] takes on 6 constant values:-

1. Tabs
2. Commas
3. Spaces
4. Semicolons
5. Nothing
6. Custom character

Here is the line of code to import a CSV file:-

objXL.WorkBooks.Open(FileName & ".csv",,,2)

I do not like using Comma's as a delimiter as they tend to crop up in many forms of data. Instead by setting the format to Custom Character (6) and using a PIPE (|) as the delimiter there is less chance that columns of data won't fall out of sequence.

Here is the line of code to import a delimited file using the PIPE (|) as the delimiter:-

objXL.WorkBooks.Open(FileName & ".txt",,,6,,,,,"|")

Here is the complete VBScript to convert a CSV file to an XLS spreadsheet.

Option Explicit

Const FileName = "YourPath\YourFileName"

'***********************************************************

DIM objXL, objWB

Set objXL = WScript.CreateObject ("Excel.Application")
Set objWB = objXL.WorkBooks.Open(FileName & ".csv",,,2)

objXL.DisplayAlerts = False

objWB.SaveAs FileName & ".xls"

objXL.Quit()

Set objWB = Nothing
Set objXL = Nothing

Comments on Convert CSV to XLS (0)