Joint Technologies ltd

Batch 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 Tools|macro|macros…
click on the macro name (testme01, but you can rename it!)
and then click run.

This entry was posted on Wednesday, May 2nd, 2007 at 8:17 pm and is filed under Tech Stuff, Today a Client Asked.... You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.