How to import .any. csv file through program VBA

[Jacko1996]

So Im not sure what to do with this. Im trying to make VBA to import any .csv files into excel. PLEASE HELP

Someone told me to use this

' Open GetOpenFilename with the file filters.
Fname = Application.GetOpenFilename( _
FileFilter:="csv Files (*.csv), *.csv", _
Title:="Select a file or files", _
MultiSelect:=False)

and put that in a loop statement around your import csv macro.
So just change the hard-coded csv part in your recorded macro for importing CSV's to fname and job done

This is my hard-coded macro for a random .csv

Sub Macro1()
'
' Macro1 Macro
'


'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\CSV Files\IDCJAC0009_040157_1800_Data.csv", Destination:=Range( _
"$A$1"))
.CommandType = 0
.Name = "IDCJAC0009_040157_1800_Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End Sub

 

[CharlesChuckieCharles]

The following loads a chosen file into the current Activesheet

If you want to do the multiple times how will each file be handled,, a new sheet for each file ( or work book )

also is it every csv in a folder/Directory or is it easier to look for the file each time

Sub Macro1()
'
' Macro1 Macro
'


' Open GetOpenFilename with the file filters.
fname = Application.GetOpenFilename(FileFilter:="csv Files (*.csv), *.csv", Title:="Select a file or files", MultiSelect:=False)

'and put that in a loop statement around your import csv macro.
'So just change the hard-coded csv part in your recorded macro for importing CSV's to fname and job done

'This is my hard-coded macro for a random .csv

FileNameAlone = Right(Left(fname, Len(fname) - 4), (Len(fname) - 4) - (InStrRev(fname, "\", -1)))

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fname, Destination:=Range( _
"$A$1"))
.Name = FileNameAlone
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

 

[Jacko1996]

Thanks that works. However how am I able to make it load into separate sheets.