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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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