This Excel tutorial explains how to convert CSV to Excel in Excel VBA.
You may also want to read:
Excel VBA Import CSV into Excel using Workbooks.OpenText Method
Convert CSV to Excel in Excel VBA
CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. This tutorial demonstrates how to write a Macro to convert all CSV files in a folder to Excel.
VBA Code – Convert CSV to Excel
You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine
Create a new workbook and copy the file to a new Module, change the desired folder path highlighted in blue.
Basically what the Macro does is to open csv in Excel and then save as xlsx (xlOpenXMLWorkbook) under specified folder.
Public Sub csvToxls() Dim FSO As Object Dim folder As Object Dim wb As Object csvPath = "C:\test\" xlsPath = "C:\test\xlsFolder" Set FSO = CreateObject("Scripting.FileSystemObject") Set csvFolder = FSO.GetFolder(csvPath) If FSO.FolderExists(xlsPath) = False Then FSO.createFolder (xlsPath) End If Set xlsFolder = FSO.GetFolder(xlsPath) With Application .DisplayAlerts = False .ScreenUpdating = False End With For Each wb In csvFolder.Files If LCase(Right(wb.Name, 3)) = "csv" Then Set activeWB = Workbooks.Open(wb) activeWB.SaveAs Filename:=xlsPath & "\" & Left(activeWB.Name, Len(activeWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook activeWB.Close True End If Next With Application .DisplayAlerts = True .ScreenUpdating = True End With End Sub
Example – Convert CSV to Excel
Suppose we have two csv files under C:\test\
Create a workbook and copy the Macro, then run the Macro
A new folder called xlsFolder is created under C:\test\ , and two workbooks are generated in xlsx format.