Excel VBA convert CSV to Excel

This Excel tutorial explains how to convert CSV to Excel in Excel VBA.

You may also want to read:

Access Excel FSO File Methods

Import Chinese CSV to Excel

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

loop_through_workbook

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.

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *