VBA Convert XLSX to CSV Files - Entire Directory

mtgingrass

2017/10/03

Convert File Extensions.

Sometimes, you find yourself converting one file extension to another for various reasons. Many software programs will not read files such as a .xlsx or .xlsm Excel file. They will, however, easily read a .csv file.

In this tutorial, I will show you how to use an already created macro to convert an entire directory of files to a new file extensions almost instantly. Saves a tremendous amount of time using macros for this.

In this tutorial you will learn:

Watch the Video for a step by step guide on how to use this macro.

[embed]https://youtu.be/Pzk1Iq5jSxo[/embed]

Code

Copy and paste the code below to get you started.

<code>
Option Explicit

Sub ConvertToCsv()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim myPath As String
    Dim myFile As String
    Dim myExt As String
    Dim NewWBName As String
    Dim ChooseFolder As FileDialog

    'Optimize
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual

    'Retrieve Target Folder Path From User
    Set ChooseFolder = Application.FileDialog(msoFileDialogFolderPicker)

    ChooseFolder.Title = "Select Target Path"
    ChooseFolder.AllowMultiSelect = False

    If ChooseFolder.Show <> -1 Then GoTo NextCode
        myPath = ChooseFolder.SelectedItems(1) & "\"

    'Cancel
NextCode:
    myPath = myPath
    If myPath = "" Then Exit Sub

    'File Ext to Change
    myExt = "*.xls*"

    'Target Path with Ending Extention
    myFile = Dir(myPath & myExt)

    'Loop through each Excel file in folder
    Do While myFile <> ""
        'Set variable equal to opened workbook
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
        NewWBName = myPath & Left(myFile, InStr(1, myFile, ".") - 1) & ".csv"
        ActiveWorkbook.SaveAs Filename:=NewWBName, FileFormat:=xlCSV
        ActiveWorkbook.Close savechanges:=True
        'Get next file name
        myFile = Dir
    Loop

    'Reset Macro Optimization Settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub</code>
comments powered by Disqus