Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Using Windows, I need a script to convert all the (xls) files of a directory to csv.

share|improve this question
    
What version of windows? – EBGreen Sep 27 '11 at 14:59
    
Can you give an example of an input and desired output? – bbaja42 Sep 27 '11 at 14:59
    
@EBGreen: Windows XP – Isaac Kleinman Sep 27 '11 at 15:09
    
In that case either of the VBScripts below should work fine. – EBGreen Sep 27 '11 at 15:25
up vote 1 down vote accepted

I found this VB Script here

option explicit

dim a, arg, oArgs, ArgNum
a = 0

Set oArgs = WSCript.arguments
ArgNum = oArgs.Count

if ArgNum <> 1 then
   WSCript.echo "Syntax: cscript <Script> filename_root"
   WScript.quit(1)
end if

dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)

rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
 oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if

pos = instr(oldfilename, " ")

if pos > 0 then

 newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
 newname = replace(newname," ","_")
 filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname

 dim oShell
 set oShell = Wscript.CreateObject("WScript.Shell")
 Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
 oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"

else
 filename_root = oldfilename
end if

dim app
set app = createobject("Excel.Application")

dim wb
set wb = app.workbooks.open( filename_root & ".xls" )

const xlXMLSpreadsheet = 46
const xlCSV = 6

app.DisplayAlerts = false
dim sht
for each sht in wb.worksheets
 sht.activate
 dim output_filename
 output_filename = filename_root & "_" & replace( sht.name, " ", "_" ) & ".csv"
 wb.saveAs output_filename, xlCSV
next
'wb.saveAs filename_root & ".xml", xlXMLSpreadsheet
app.DisplayAlerts = true

wb.close false

'app.close

WScript.quit
share|improve this answer
    
I ended up doing it manually, but I'll have this for next time! – Isaac Kleinman Sep 28 '11 at 13:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .