.csv to .xls conversion using VBS

[kyle_engineer]

Hello. 

So I'm trying to automate a .csv to .xls conversion. I have this little snippet (below) that works to get the data into an .xls file, the problem is that it doesn't actually utilize the "," delim. So, while this opens the .csv in Excel and re-saves it, I need it to actually use the "Get External Data -> Text File..." method. Anyway, any help is appreciated.

Code: [Select]

Dim myXL
Set myXL=CreateObject ("Excel.Application")

myXL.WorkBooks.Open "C:\DIR\file.csv"

myXL.DisplayAlerts=False 'To keep Excel hidden
myXL.ActiveWorkbook.SaveAs "C:\DIR\file.xls", -4143 'IDK what the "-4143" does
myXL.DisplayAlerts=True

myXL.ActiveWorkbook.Close False
myXL.Quit

Set myXL = Nothing

NOTE: If there are any typos, know that they are correct in the actual .vbs.

[kyle_engineer]

Alright, I fixed it.

This works:

Code: [Select]

Dim myXL
Const xlDelimited = 1
Const xlWorkbookNormal = -4143

Set myXL=CreateObject("Excel.Application")
myXL.Visible=False
myXL.WorkBooks.OpenText "C:\DIR\file.csv", , , xlDelimited, , , , , True 'Change the source path as needed.

myXL.DisplayAlerts=False
myXL.ActiveWorkbook.SaveAs "C:\DIR\new_file.xls", xlWorkbookNormal 'Change the destination path as needed.
myXL.DisplayAlerts=True

myXL.ActiveWorkbook.Close False
myXL.Quit

Set myXL = Nothing
So if anyone needs something similar, here you go!

NOTE: This is also for Excel 2000. Don't know how it integrates with newer versions of Excel or newer VB and/or .NET Frameworks. Also, was built on XP Pro SP3, but that shouldn't matter much. Anyway, ENJOY!