Solved

Convert CSV to XLS including cell number formatting

Posted on 2008-06-10
8,441 Views
Hi,

I use the code below to convert CSV files into XLS files, I call this using a batch file.

I have two problems,

1) One of my columns contains 13 digit numbers and in Excel these display like "4.71084E+12" and not "4710836936966" - because the cell format is General.  I would like to be able to specify the cell format is text

2) I need to explicitly pass the name of the input and output files in this VB script, I would like to be able to pass these as parameters when I call the VB in my batch file.

Any help greatly appreciated,

Rob

ps. I found this which may give some insight towards issuue 1 - http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_22650457.html?sfQueryTermInfo=1+csv+format+xl
dim oEx

set oEx=Createobject("Excel.Application")

oEx.Workbooks.Open "C:\testing\in.csv"

oEx.ActiveWorkbook.SaveAs "C:\testing\out.xls", -4143, , , False, False

oEx.Quit

0
Question by:arundelr
• 2

Author Comment

The below code is doing what I need to clear up issue 1

I would still like to be able to pass the name of the input file to the VBS script rather than hard coding it.

Can anybody help with that?

i,.e. test.vbs -inputfile=c:\testing\in.csv

Thanks
Option Explicit

Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory

Dim objExcel

strOrigFile = "C:\testing\in.csv"

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

'CONVERTS THE FILE TO AN EXCEL FILE

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

strFile = replace(strOrigFile,".csv",".xls")

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open strOrigFile

objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

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

'FORMAT THE EXCEL FILE AND SAVE IT

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

objExcel.Workbooks.Open strFile

'Set the number format to zero D.P

objExcel.columns("A:A").numberformat="0"

objExcel.columns("C:C").numberformat="0"

objExcel.columns("D:D").numberformat="0"

objExcel.ActiveWorkbook.SaveAs strFile, -4143

objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

Set objExcel = Nothing

'msgbox "FINISHED!!!"

0

Accepted Solution

arundelr earned 0 total points
0

Featured Post

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.