Excel 2010 Macro Import from CSV

 

If you notice that you are importing a lot of comma-separated variable (CSV) files and having to go back and delete them, this program is for you.


To quickly open a CSV in Excel and permanently delete original file:

Sub OpenLargeCSVFast()

    Dim buf(1 To 16384) As Variant

    Dim i As Long

    ‘Change the file location and name here

    Const strFilePath As String = “C:\temp\Test.CSV”

    Dim strRenamedPath As String

strRenamedPath = Split(strFilePath, “.”)(0) & “txt”

    With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

    End With

    ‘Setting an array for FieldInfo to open CSV

    For i = 1 To 16384

buf(i) = Array(i, 2)

    Next

    Name strFilePath As strRenamedPath

    Workbooks.OpenText Filename:=strRenamedPath, DataType:=xlDelimited, _

                       Comma:=True, FieldInfo:=buf

    Erase buf

    ActiveSheet.UsedRange.Copy ThisWorkbook.Sheets(1).Range(“A1”)

    ActiveWorkbook.Close False

    Kill strRenamedPath

    With Application

        .ScreenUpdating = True

        .DisplayAlerts = True

    End With

End Sub