Macro to Import and Export Text Files (CSV)

These VBA macros export the active sheet as a CSV file, and import a CSV or text file into the active sheet, in one click each.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Export the active sheet as CSV

Sub ExportSheetToCSV()
    Dim f As String
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    f = ActiveWorkbook.Path & Application.PathSeparator & ActiveSheet.Name & ".csv"
    ActiveSheet.Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=f, FileFormat:=xlCSVUTF8
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    MsgBox "Saved: " & f
End Sub

ActiveSheet.Copy puts the sheet into a new workbook, which is saved as CSV and closed, so your original file stays as it is. xlCSVUTF8 keeps ₹ and Malayalam or Hindi text readable. In Excel 2013 or earlier, use xlCSV instead.

Import a CSV or text file

Pick the file, and its contents are copied into the active sheet starting at A1:

Sub ImportTextFile()
    Dim f As Variant, dest As Worksheet, wb As Workbook
    f = Application.GetOpenFilename("Text files (*.csv;*.txt), *.csv;*.txt")
    If f = False Then Exit Sub
    Set dest = ActiveSheet
    Set wb = Workbooks.Open(f)
    wb.Sheets(1).UsedRange.Copy dest.Range("A1")
    wb.Close SaveChanges:=False
End Sub

Excel reads the file with its usual rules, so values like PIN code 0682 may lose their leading zero.

Without a macro

  1. Export: File → Save As → choose CSV UTF-8 (Comma delimited).
  2. Import: Data → From Text/CSV, pick the file, then Load. Choose Transform Data to set columns such as PIN codes to Text.

For one-off conversions in the browser, there’s also the free CSV to Excel converter, which keeps leading zeros.

Things to know

  • CSV files hold one sheet of values only: no formulas, formatting or other sheets.
  • An existing CSV with the same name is replaced without a warning, because alerts are turned off while saving.

Related macros