Convert CSV (semicolon separated) to XLS using VBS

[Senpe]

Is it possible to do it reverse way ?

I need to convert csv file

DATE;DEPART;CLA;SERV;ERRE;MESE;SCHEE;TYP;DUREE;TAI ;FICH;RE;Date o;Date f;Com 2012-05-07;21:02:04; da_nbondv02_data;nbondv02;0;OK;lmejvd-diff-2sem;da;00:02:20;41272;32;2 Sem 2012-05-07;21:00:00; da_nbondv02_sys;nbondv02;0;OK;lmmjvd-diff-2sem;da;00:04:04;356070;2109;2 Sem 2012-05-07;21:01:39; da_nbondv03_data;nbondv03;0;OK;lmejvd-diff-2sem;da;00:07:55;224387;3335;2 Sem

in to XLS.

I used this code

Function ok()
set ce = CreateObject("Excel.Application")

newFileName="C:\BE\testnew.xls"
ce.Workbooks.Open "C:\BE\1.csv"
ce.ActiveWorkbook.SaveAs newFileName
ce.ActiveWorkbook.Close(0)
ce.Quit
Set ce = Nothing

end function

It's converting to XLS but without columns, just row by row. I need to use VB Script to do it as function and remember that all " ; " starting with new column in XLS

Please help.

[Guido Geurs]

Q1: Is the data you entered = 1 row in XLS?
Q2: are there a lot of rows? more than +-200 ?

1st solution: (a few rows)
- Read a line of the data.
- Split the line on ";" in an array
- put the array in the next empty row on the sheet.

2nd solution:( a lot of rows)
- read all the data in an array
- split the data from the first array in a second array.
- dump the second array in the sheet.

[Killer42]

You don't need to split up the data yourself; Excel will do that for you.

Try this VBA statement...

Workbooks.OpenText Filename:= _
"C:\BE\1.csv", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False