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.