Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 0 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.