How to get data from multiple CSV or text files into Excel worksheet automatically with VBA.
Here’s the complete VBA code:
Option Explicit
Const sPath = “C:\diya-takyar\” ‘declare your path
Const delim = “,”
Sub GetDataFromMultipleCSVFiles()
‘declare a few variables
Dim sFile As String
Dim sRecord As String
Dim arrRecord()
Dim fNum As Integer
Dim RowCounter As Long
Dim i As Long
‘—> INPUT
On Error GoTo errhandler
sFile = Dir(sPath & “*.csv”)
RowCounter = 0
‘run loop
Do While sFile <> “”
fNum = FreeFile
Open sPath & sFile For Input As #fNum
‘—> PROCESS
Do While Not EOF(fNum)
Line Input #fNum, sRecord
sRecord = sFile & delim & sRecord
RowCounter = RowCounter + 1
ReDim Preserve arrRecord(1 To RowCounter)
arrRecord(RowCounter) = Split(sRecord, delim)
Loop
Close #fNum
sFile = Dir()
Loop
‘—> OUTPUT
With ThisWorkbook.Sheets(“Sheet1”).Range(“A1”)
For i = 1 To RowCounter
.Offset(i – 1).Resize(, UBound(arrRecord(i)) + 1).Value = arrRecord(i)
Next i
End With
errExit:
Reset
Exit Sub
errhandler:
Resume errExit
End Sub
