Conversions‎ > ‎

WBs to CSVs

 
 
PROBLEM: "I want to convert all xls files in a single folder to CSV files and save in the same folder."

SPECIFICATIONS:
  1. Must be Excel 2003+ compatible (no FileSearch)
  2. Path definable
  3. Type of file to convert definable
  4. Code to edit is highlighted in red

CODE

Option Explicit

Sub WorkbooksToCSV()
'Author:    Jerry Beaucaire
'Date:      4/25/2010
'Save each workbook to an individual CSV file
'Assumes each workbook only has one sheet
Dim wb      As Workbook
Dim OldDir  As String
Dim fPath   As String
Dim fName   As String

'Setup
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
   
'Path and filename (edit this section to suit)
    fPath = "C:\2010\Test\"             'remember final \ in this string
    OldDir = CurDir                     'memorizes the users current working path
    ChDir fPath                         'activate the filepath with files to import
    fName = Dir("*.xls")                'listing of desired files, edit filter as desired
   
'open each found file
    Do While Len(fName) > 0
    'make sure THIS file isn't accidentally reopened
        If fName <> ThisWorkbook.Name Then
        'Open file
            Set wb = Workbooks.Open(fPath & fName)
            wb.SaveAs Filename:=fPath & _
                Left(wb.Name, Len(wb.Name) - 4) & ".csv", FileFormat:=xlCSVMSDOS, _
                CreateBackup:=False
            wb.Close SaveChanges:=False
        End If
    'ready next filename, reassert the list since a file was moved
        fName = Dir
    Loop

'Cleanup
    ChDir OldDir         'restores user's original working path
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub




Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments