Excel VBA to Convert CSV File to XLSX (2 Easy Examples)

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.


Download Practice Book

Download the practice book here.


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.


2 Examples of Excel VBA to Convert CSV File to XLSX File

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.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • Secondly, select Insert and then, select the Command Button icon from the ActiveX Controls.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • Thirdly, draw the command button on the active sheet like the picture below.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • 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

Use Command Button to Convert CSV File to XLSX File with Excel VBA

You can add this code in the Module window also. 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.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • 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.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • After clicking OK, the CSV file will open in the Excel application.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • 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.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

  • Finally, you will see the converted XLSX file in the same folder.

Use Command Button to Convert CSV File to XLSX File with Excel VBA

Read More: Excel VBA to Import CSV File without Opening (3 Suitable Examples)


Similar Readings


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.

Apply VBA to Transform CSV File to XLSX File in Excel

  • In the second step, select Insert and then, select Module. The Module window will appear.

Apply VBA to Transform CSV File to XLSX File in Excel

  • 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

Apply VBA to Transform CSV File to XLSX File in Excel

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.

Apply VBA to Transform CSV File to XLSX File in Excel

  • In the end, you will see the converted XLSX file in the desired location.

Read More: How to Convert CSV to XLSX without Opening (5 Easy Methods)


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.

Read More: How to Convert CSV to XLSX (4 Quick Methods)


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.

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo