Get Data from Multiple CSV Text Files into Excel Worksheet

Опубликовано: 09 Октябрь 2024
на канале: Dinesh Kumar Takyar

11,009

177

How to get data from multiple CSV or text files into Excel worksheet automatically with VBA.
Details also available at https://www.exceltrainingvideos.com/g...
Below is the complete VBA code:
Option Explicit

Const sPath = "C:\diya-takyar\"
Const delim = ","

Sub MergeDataFromMultipleCSVFiles()
'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 & "*.txt")

RowCounter = 0
'run loop
Do While sFile NOT EQUAL TO ""
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

NOTE: Replace NOT EQUAL TO with angular brackets.