Convert to CSV from commandline

  • Thread starter Thread starter ED
  • Start date Start date
E

ED

I have spreadsheets with multiple multiple sheets which
always need to be coverted to csv for input to other
programs. How can I have it done via commandline without
doing it manually via MS Excel File -> Save As?
 
I think I'd open excel, open one of the files and then run a macro like this:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:\temp\" & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

MsgBox "done with: " & ActiveWorkbook.Name

End Sub


Then close that file and open the next.

or....

You if the excel files are all in one folder, you could just run a macro like
this:

Option Explicit
Sub testme()

Dim InFileNames As Variant
Dim tempWkbk As Workbook
Dim newWks As Worksheet
Dim wks As Worksheet
Dim fCtr As Long

InFileNames = Application.GetOpenFilename _
(FileFilter:="Excel Files, *.xls", MultiSelect:=True)

If IsArray(InFileNames) Then
For fCtr = LBound(InFileNames) To UBound(InFileNames)
Set tempWkbk = Workbooks.Open(Filename:=InFileNames(fCtr))
For Each wks In tempWkbk.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:\temp\" & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

'msgBox "done with: " & tempWkbk.Name
tempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub

Watch out for overwriting the .csv names. I used the same name as the
worksheet. Might cause problems if you have more than one sheet1 in all the
files.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
I have spreadsheets with multiple multiple sheets which
always need to be coverted to csv for input to other
programs. How can I have it done via commandline without
doing it manually via MS Excel File -> Save As?
Click to expand...

In theory, the Perl programming language provides this functionality, but it
requires modules for reading and writing Excel files, and those modules may not
be part of standard Perl installs. Odds are that Excel would be faster doing
this than Perl would.
 
Another option would be to append the date/time to the filename.
..SaveAs Filename:="C:\temp\" & wks.Name _
& "_" & format(now,"yyyymmdd_hhmmss"), FileFormat:=xlCSV

or maybe even the original workbook name:
..SaveAs Filename:="C:\temp\" & tempwkbk.name & "_" & wks.Name, _
FileFormat:=xlCSV
 
Back
Top