Convert .csv file into Excel in VBScript

Question

I'm writing a program to convert a .csv file into an Excel file in VBScript.I'm working with Excel 2010 and VBScript.

This is the portion of my .csv file (It has a header as well)

Report Title,Goods Performance Characteristics Report,,Report Title,Goods Performance Characteristics Report,,,,,,, Begin Date,8/7/2012,,,,,,,,,, Template Name,XXXX-Test-VVV-Two-Positions,,,,,,,,,, Total Productions,,,,,,,,,,,

,ID,Name,Product Description,Issue Date,Product Type,Weight (kg),Weight (lb),Price,Product volume, Profit,Total Value (000) ,315616102,Lux Honey,Body Wash,8/1/2012,C,0.06,0.06,93,793920,0,7455703038 ,579780206,Clear AntiDandruf,Shampoo,8/1/2012,C,0.06,0.06,60.5,1325980,2.05,8022179000 ,761713106,Loreal Divine,Face Crub,8/1/2012,F,0.12,0.12,46.52,3314780,5.07,15420356560

My VBScript code

Dim xlApp, workBook1  

Set xlApp = CreateObject("Excel.Application")  

Set workBook1 = xlApp.WorkBooks.OpenText(

"Y:\Personal Folders\Yasoda\TestFile1.csv", , ,xlDelimited, ,false ,false ,false ,true,false ,false ,"~")

Set workBook1 = xlApp.ActiveWorkBook

workBook1.SaveAs("D:\Personal Folders\XXXXX\x.xls",xlNormal)  

workBook1.Close

But I'm getting an error saying "Unable to get the OpenText property of the WorkBooks class".

Any help is appreciated.

Thanks

Answer 1

I did not get any file, please check the email address.

Andreas.

Answer 2

Thanks alot Andreas.I'll mail you the code and a snapshot of the error

Answer 3

Then there are 2 possibilities:

a) The AddIn code is not protected, open the VBA editor and have a look at the code by yourself.

b) I need to reproduce the error. Look in my profile for my email adress and instructions and send me your Excel and CSV file and tell me in details what you did when the error occurs.

I'll look at the files Monday morning and notify you immediately, or fix the error.

BTW, the AddIn also supports macro recording, so if you install the AddIn permanently you can automate the process with a short macro.

Andreas.

Answer 4

Hi Andreas,

Thank you for the replay.I tried this and it worked.But after few times the .xla stopped running.An error occured.

Anyways I'm looking for a VBScript code so I could automate the conversion process.

Answer 5

Download this file:

http://dl.dropbox.com/u/35239054/CSV.xla

Open Excel

Open the windows explorer and double click the AddIn to load it into Excel.

Right click the cell where the CSV file should be imported.

If the context menu opens, go down and select "CSV\Import a CSV"

Select your CSV file and click open.

If the data isn't separated correctly, please check the CSV options and try again.

Andreas.