how to convert multiple XLS files to CSV?

[Mr.P.]

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

[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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Mr.P." <Mr.P.@discussions.microsoft.com> wrote in message news:5917E149-4BB8-46E2-B857-38F6C3156510@microsoft.com...
> Does anyone knows how to convert about 1000 XLS files to CSV files in one go??
> I am using Office XP Pro.

[Ron de Bruin]

Oops

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:%23jfEUAaUGHA.1204@TK2MSFTNGP12.phx.gbl...
> 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
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Mr.P." <Mr.P.@discussions.microsoft.com> wrote in message news:5917E149-4BB8-46E2-B857-38F6C3156510@microsoft.com...
>> Does anyone knows how to convert about 1000 XLS files to CSV files in one go??
>> I am using Office XP Pro.
>
>

[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

"Mr.P." <Mr.P.@discussions.microsoft.com> wrote in message
news:5917E149-4BB8-46E2-B857-38F6C3156510@microsoft.com...
> Does anyone knows how to convert about 1000 XLS files to CSV
> files in one go??
> I am using Office XP Pro.

[Pocketchange292]

Thank you for this!!

But I am trying to tweak this a little...

I have changed the source folder, but now I am trying to save to another separate specific folder.

For instance, MyPath = "X:\scop\space management\GroupQueries" all good there, but I want my Save to folder as "C:\Data\GroupQueries".

Any help is much appreciated. Thank you in advance!

[Alf]

Perhaps something like this could work for you?

Option Explicit

Sub conV_excel_csv()

Dim wb As Workbook
Dim fPath As String
Dim tPath As String
Dim sFile As String



sFile = Dir("X:\scop\space management\GroupQueries\*.xls")
fPath = "X:\scop\space management\GroupQueries\"
tPath = "C:\Data\GroupQueries\"

Do While sFile <> ""

    Set wb = Workbooks.Open(fPath & sFile)
    wb.SaveAs Filename:=tPath & Mid(sFile, 1, Len(sFile) - 4) & ".CSV", FileFormat:=xlCSV, CreateBackup:=False
     
    wb.Close SaveChanges:=False
    
    sFile = Dir
    
    Loop

End Sub

This will convert the active sheet in the excel file to a CSV file. If your excel files have more sheets and you wish all sheets to CSV files you have to loop through all sheets and convert them one at a time as far as I know.

The trimming of the file name "Len(sFile) - 4" will remove the ".xls" part of the file and replace it with ".CSV".

Alf