how to convert multiple XLS files to CSV?

M

Mr.P.

Does anyone knows how to convert about 1000 XLS files to CSV files in one go??
I am using Office XP Pro.
 
R

Ron de Bruin

You can open them in a loop and save them as CSV
Have all workbooks one sheet ?

Try this one that copy the first sheet and save it as CSV

Sub Copyrange_1()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim wb As Workbook
SaveDriveDir = CurDir

MyPath = "C:\Data"
'Add a slash at the end if the user forget
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)

mybook.Sheets(1).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs MyPath & "CSV-" & Left(FNames, Len(FNames) - 4), FileFormat:=xlCSV
.Close False
End With

mybook.Close False
FNames = Dir()
Loop

CleanUp:
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
R

Ron de Bruin

Oops

This is working for all the files that are in the folder C:\Data
Change to your folder
 
C

Chip Pearson

If all the XLS files are in the same folder, and the folder
contains only the XLS files to convert, use code like the
following:

Dim FName As String
Dim WB As Workbook

ChDrive "H" '<<< CHANGE
ChDir "H:\Test" '<<< CHANGE

FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(Filename:=FName)
WB.SaveAs Filename:=Replace(FName, ".xls", ".csv"),
FileFormat:=xlCSV
WB.Close savechanges:=True
FName = Dir()
Loop



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top