Batch convert XLS to CSV
Wednesday, May 2nd, 2007Batch convert XLS to CSV.
This worked for me in Excel 2007. Works great. I saw software that does this for $19.99 up to $120.99.
After this you can Merge the CSVs into one file.
Originally posted to a google group by Dave Peterson.
Option Explicit
Sub testme01()
Application.ScreenUpdating = False
Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim logWks As Worksheet
Dim tempName As String
Dim wks As Worksheet
Dim oRow As Long
‘change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
Set logWks = Workbooks.Add(1).Worksheets(1)
logWks.Range("a1").Resize(1, 3).Value _
= Array("WkbkName", "WkSheetName", "CSV Name")
‘get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop
If fCtr > 0 Then
oRow = 1
For fCtr = LBound(myFiles) To UBound(myFiles)
Set tempWkbk = Nothing
On Error Resume Next
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
On Error GoTo 0
If tempWkbk Is Nothing Then
logWks.Cells(oRow, "A").Value = "Error Opening: " _
& myFiles(fCtr)
oRow = oRow + 1
Else
For Each wks In tempWkbk.Worksheets
With wks
If Application.CountA(.UsedRange) = 0 Then
‘do nothing
Else
.Copy ‘to a new workbook
tempName = myPath & Trim(.Name) & ".csv"
Do
If Dir(tempName) = "" Then
Exit Do
Else
tempName = myPath & Trim(.Name) & "_" _
& Format(Time, "hhmmss") & ".csv"
End If
Loop
oRow = oRow + 1
With ActiveWorkbook
.SaveAs Filename:=tempName, FileFormat:=xlCSV
.Close savechanges:=False
End With
logWks.Cells(oRow, "A").Value = myFiles(fCtr)
logWks.Cells(oRow, "b").Value = .Name
logWks.Cells(oRow, "C").Value = tempName
End If
End With
Next wks
tempWkbk.Close savechanges:=False
End If
Next fCtr
End If
With logWks.UsedRange
.AutoFilter
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub
If you’re new to macros, you may want to read David McRitchie’s intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Start a new workbook
Hit alt-f11 to get to the VBE (where macros/UDF’s live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Modify the folder that contains the .xls files.
myPath = "c:\my documents\excel\test"
to what you need.
Now go back to excel.
Save your workbook (so you can run the macro)
When you want to run or rerun:
Open this workbook
(fix the folder name in the code if it changed)
click on the macro name (testme01, but you can rename it!)
and then click run.