Excel VBA open CSV file and import

Excel VBA can be used to import a CSV file into a sheet very easily.  The Excel VBA code generated is mirrored in the Excel GUI by using the Data menu, Import External Data option, and the Import Data function.

Importing a CSV file directly into Excel using VBA is a great way to allow you to interface with external data systems easier, rather than doing a manual copy and paste.  Sometimes you just don't have the ability to do a web query on everything.

Here is the sample code:

 

' filename = CSV filename without directory (test.csv)
' outSheet = name of the worksheet in the current workbook
'            where the data should go, will start in A1
Function doFileQuery(filename As String, outSheet As String) As Boolean
    Dim rootDir As String
    rootDir = "C:\myDirectory"
    Dim connectionName As String
    connectionName = "TEXT;" + rootDir + "\" + filename
    With Worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=Worksheets(outSheet).Range("A1"))
        .Name = filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With
End Function

If you have a file other than a CSV file, just change the Excel VBA options (.TextFileTabDelimiter, .TextFileSemicolonDelimiter, etc..) to True and the (.TextFileCommaDelimiter) to False.

Comments

import only number of columns

nice jeremy.zerrbut if i want to browse and locate my csv file and import only first 4 columns in exsisting worksheet then what will be the code?

problems in using function doFileQuery

1. function was not recognized directly from excel by using =doFileQuery(mytoll.csv,myoutput)2. in the dummy sub using the function doFileQuery i got in With worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=worksheets(outSheet).range("A1"))the message:run time error 91object variable or with block variable not set

Thanks

Thats a great code. It worked perfectly.I used it to get a csv file hosted on website then also it worked correct with out need for any change in code. Thanks for sharing.

Good one, this saved me a lot

Good one, this saved me a lot of time, thanks Ghazi

Thanks it works great!!

Very helpful. it worked like charm for me!!