1

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

4
  • What version of windows?
    – EBGreen
    Sep 27, 2011 at 14:59
  • Can you give an example of an input and desired output?
    – bbaja42
    Sep 27, 2011 at 14:59
  • @EBGreen: Windows XP Sep 27, 2011 at 15:09
  • In that case either of the VBScripts below should work fine.
    – EBGreen
    Sep 27, 2011 at 15:25

1 Answer 1

1

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
1
  • I ended up doing it manually, but I'll have this for next time! Sep 28, 2011 at 13:57

You must log in to answer this question.

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