In this article, we will learn to convert a CSV file to an XLSX file with Excel VBA. We can perform this task manually. But with VBA, we can convert multiple CSV files at the same time. CSV files can cause some problems while opening the dataset. Today, we will demonstrate 2 examples of Excel VBA to convert CSV files to XLSX files. So, without further ado, let’s start the discussion.
What Is a CSV File?
CSV stands for Comma Separated Values. It contains a list of data. Often, this type of file is used to exchange data between applications. It is a text file. The values are separated by commas. You can view this type of file with a text editor or with Microsoft Excel.
What Is an XLSX File?
An XLSX file is a file that contains data or information in a spreadsheet. The .xlsx extension is generally used. These types of files are opened with the Microsoft Excel application. You can also open these files with other spreadsheet apps. These files are used to store different types of data in cells. You can edit the cells anytime you want.
How to Convert CSV File to XLSX File with Excel VBA: 2 Effective Examples
To explain the examples, we will use a CSV file that contains information about the Name, ID, and Date of Birth of some employees. We will convert this CSV file to an XLSX file.
1. Use Command Button to Convert CSV File to XLSX File with Excel VBA
In the first method, we will use a command button to convert a CSV file to an XLSX file. We embed the VBA code inside the command button. You can also insert it directly in the Visual Basic window. So, without any delay, let’s follow the steps below to learn the whole method.
STEPS:
- In the first place, go to the Developer tab and select Design Mode.
- Secondly, select Insert and then, select the Command Button icon from the ActiveX Controls.
- Thirdly, draw the command button on the active sheet like the picture below.
- After that, double-click on the command button to open the Code window.
- Then, type the code in the Code window:
Private Sub CommandButton1_Click()
Dim x As FileDialog
Dim xPath As String
Dim CSVfile As String
Dim Wks As String
Application.DisplayAlerts = False
Application.StatusBar = True
Wks = ActiveWorkbook.Name
Set x = Application.FileDialog(msoFileDialogFolderPicker)
x.Title = "Select a folder:"
If x.Show = -1 Then
xPath = x.SelectedItems(1)
Else
Exit Sub
End If
If Right(xPath, 1) <> "\" Then xPath = xPath + "\"
CSVfile = Dir(xPath & "*.csv")
Do While CSVfile <> ""
Application.StatusBar = "Converting: " & CSVfile
Workbooks.Open Filename:=xPath & CSVfile
ActiveWorkbook.SaveAs Replace(xPath & CSVfile, ".csv", ".xlsx", vbTextCompare), _
xlWorkbookDefault
ActiveWorkbook.Close
Windows(Wks).Activate
CSVfile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
You can add this code in the Module window. In that case, press Alt + F11 to open the Visual Basic window and then, select Insert. A drop-down menu will occur. Select Module from there and paste the code into the Module window. This process is shown in Method-2.
- Now, press Ctrl + S to save the code and then, close the Code window.
- Next, deselect the Design Mode in the Developer tab.
- In the following step, click on the command button.
- It will open a dialog box.
- Select the folder that contains the CSV file and press OK to proceed.
- After clicking OK, the CSV file will open in the Excel application.
- At this moment, go to the File tab and select Save As from there.
- Then, change the file type to Excel Workbook (*.xlsx) and save it.
- Finally, you will see the Converted XLSX file in the same folder.
2. Apply VBA to Transform CSV File to XLSX File in Excel
In the second example, you will apply VBA to transform a CSV file into an XLSX file in Excel. We will not use any command button here. Here, we will use the same CSV file. Let’s pay attention to the steps below to know more.
STEPS:
- First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- In the second step, select Insert and then, select Module. The Module window will appear.
- Now, type the code in the Module window:
Sub Convert_CSV_to_XLSX_File()
Dim w As Workbook
Set w = Workbooks.Open("D:\exceldemy\New folder (45)\Employee_info.csv")
w.SaveAs Filename:="D:\exceldemy\New folder (45)\Employee_info.xlsx", _
FileFormat:=xlWorkbookDefault, _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
To run the code successfully, you need to replace the directories (“D:\exceldemy\New folder (45)\Employee_info.csv”) and (“D:\exceldemy\New folder (45)\Employee_info.xlsx”) with your desired directories. Also, write the name of the file you want to convert carefully. Furthermore, make sure the extensions are correct.
- Next, press Ctrl + S to save the code.
- After that, press the F5 key on the keyboard to run the code.
- In the end, you will see the Converted XLSX file in the desired location.
Read More: Excel VBA to Read CSV File into Array
Convert CSV File to XLSX File without Excel VBA
You can also convert a CSV file to an XLSX file without Excel VBA. To do so, follow the steps below.
STEPS:
- Firstly, open the CSV file with the Microsoft Excel application.
- Then, go to the File tab.
- After that, select Save As.
- Next, change the Save As type to Excel Workbook (*.xlsx) and save it.
- In the end, you will see the XLSX file in the desired location.
Things to Remember
There are certain things you need to remember to convert CSV files to XLSX files using Excel VBA.
- You must write the directory and file name correctly in the VBA code.
- Also, be extra careful while writing the extensions.
- Use Method-2 to convert a single CSV file.
- You can convert multiple CSV files using Method-1.
Download Practice Book
Download the practice book here.
Conclusion
In this article, we have demonstrated 2 easy examples of Excel VBA to Convert a CSV File to an XLSX. We have also shown the process without using the VBA. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
