Hello Excellers and welcome back to another #MacroMonday #ExcelVBA blog post in my 2020 series. Have you ever needed to convert a whole folder of Excel files into CSV files? Me too!. Just last week I needed to do this. I used some great Excel VBA code which had the conversion finished in no time. So I thought it would be a great topic for our Macro Monday post.

Preparing To Write The Code.

First of all, I want to make sure I have an Input Folder and an Output folder. I have created these on my Desktop. The Input folder contains my CSV files.

Next, you will need to open the Visual Basic Editor. There are two ways to do this.

Either by hitting ALT +F11

OR

Selecting the Developer Tab | Code Group | Visual Basic.

Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If the macro is stored in the current workbook then use is restricted to that workbook. This code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook Macro Mondays

Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply ConvertToCSV. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]

Sub ConvertToCSV()

[/stextbox]

Turn Off Screen Updates And Display Alerts.

This first piece of code will prevent the screen from flashing as the code is executed and help your code run faster. I have also chosen to not display and alerts to the user. These are pop-ups that are displayed to the user such as OK to Delete Sheet?. We do not need them in this macro so, I have decided to turn them off. Note– we will be turning these settings back on at the end of the macro.

[stextbox id=’info’]

Application.ScreenUpdating = False
Application.DisplayAlerts = False

[/stextbox]

Declaring Variables.

Next, we need to declare a couple of variables. This simply creates a memory container in Excel for these values. InputCsvFile is declared as a variant. This allows the name of CSV files to be any type of name or it is not specifically declared. This data type automatically works out which data type is the most appropriate based on the value you assign. So, this makes sense when dealing with multiple Excel files that could have a myriad of names within the folder. We are declaring the input Folder as a String as well as the Output Folder.

[stextbox id=’info’]

Dim InputCsvFile As Variant, InputFolder As String, OutputFolder As String

[/stextbox]

 

The Input Folder and The Output Folder.

Next, have referred to the exact location of both the Input and Output folder. These are the folders that I created before beginning to write the VBA code.

[stextbox id=’info’]

InputFolder = “C:\Users\EXCEL\Desktop\InputData”
OutputFolder = “C:\Users\EXCEL\Desktop\OutPutData”
InputCsvFile = Dir(InputFolder & “\*.xlsx”)

[/stextbox]

Loop Through All CSV Files In The Input Folder.

I now have used the Do While loop. This requires Excel to do something if a condition is met or is true. It is like a logical function where there is a true or false condition. So, if the condition is TRUE it will keep executing the statement inside the loop but if the condition is FALSE straight away it will exit the Do While statement. In this routine, if the folder contains files (is not empty) then save those files as CSV files in the Output folder. This looping keeps going until the files are saved in the Output folder as CSV files.

[stextbox id=’info’]

Do While InputCsvFile <> “”
Workbooks.OpenText Filename:=InputFolder & “\” & InputCsvFile, DataType:=xlDelimited, Comma:=True
ActiveWorkbook.SaveAs Filename:=OutputFolder & “\” & Replace(ActiveWorkbook.Name, “.xlsx”, “.csv”), FileFormat:=xlCSV, CreateBackup:=False

ActiveWorkbook.Close
InputCsvFile = Dir
Loop

[/stextbox]

Ending The Macro.

The routine then ends with the End Sub piece of code. This is already entered into the module for us when started the type the name of the Macro. [stextbox id=’info’] End Sub [/stextbox]

 

 

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.