Solved

Convert CSV to XLS including cell number formatting

Posted on 2008-06-10
8,453 Views
Last Modified: 2008-06-10
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
2 Comments

Author Comment

ID: 21749784
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!!!"

0

Accepted Solution

arundelr earned 0 total points
ID: 21751941
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Simple Linear Regression
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.