• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8505
  • Last Modified:

Convert CSV to XLS including cell number formatting

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

Open in new window

0
arundelr
Asked:
arundelr
  • 2
1 Solution
 
arundelrAuthor Commented:
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.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
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.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
 
'msgbox "FINISHED!!!"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now