Macro to Copy a Specific Column Values From Different csv Files into a Single Worksheet

[Ay Sticky]

Good day experts, I need to fetch values from an identified column (DistanceToNext), across numerous csv files into a single worksheet. These csv files are indexed with numbers. The first csv file indexed 001 is to be pasted in first column of the single worksheet. The second csv indexed 002 to be pasted in second column of the single sheet and so on. All files with samples can be obtained in the link below, including a text file describing the whole task because the tasks seems more than what I have explained here.

New folder

Shared with Dropbox

www.dropbox.com

 

[Ay Sticky]

A VBA demonstration for starters :​

Sub Demo1()
    Dim P$, C$, L&, V(), F%, K&, N&, S$(), R&
        P = ThisWorkbook.Path & "\GbonagunNodeMeasurements\"
        C = Dir$(P & "*.csv"):  If C = "" Then Beep: Exit Sub
        While C > "":  L = L + 1:  C = Dir$:  Wend
        ReDim V(1 To L + 1, 1 To L + 1)
        V(L + 1, L + 1) = 0
   With Application
       .StatusBar = "       Processing " & L & " csv files …"
        F = FreeFile
        C = Dir$(P & "*.csv")
    Do
        K = 0
        N = N + 1
        V(N, N) = 0
        Open P & C For Input As #F
        S = Split(Input(LOF(F), #F), vbLf)
        Close #F
    For R = 4 To .Min(.Match("""#*", S, 0) - 5, 3 + L * 2) Step 2
        K = K + 1
        V(K + N, N) = Round(Val(Split(Split(S(R), ",")(4), """")(1)), 2)
        V(N, K + N) = V(K + N, N)
    Next
        L = L - 1
        C = Dir$:  If N Mod 300 = 0 Then DoEvents
    Loop Until C = ""
       .SheetsInNewWorkbook = 1
       .StatusBar = False
        Workbooks.Add.Sheets(1).[A1].Resize(N + 1, N + 1).Value2 = V
       .DisplayAlerts = False
        ActiveWorkbook.SaveAs P & "CSV Import ", 51
       .DisplayAlerts = True
   End With
End Sub

After running the code, all I heard is the normal warning beeping sound. No changes, no error highlighted, no display of anything new, nothing nothing.

 

[Marc L]

As it works a treat on my side just see the codeline where the statement Beep is in order to understand your issue :​

you just forgot to update the path within the code … Should I remind « Coding can't be guessing ! »​

​

All the csv source files are in the folder "GbonagunNodeMeasurements"

The better explanation & attachment, the better code !​

 

[Ay Sticky]

A VBA demonstration for starters :​

Sub Demo1()
    Dim P$, C$, L&, V(), F%, K&, N&, S$(), R&
        P = ThisWorkbook.Path & "\GbonagunNodeMeasurements\"
        C = Dir$(P & "*.csv"):  If C = "" Then Beep: Exit Sub
        While C > "":  L = L + 1:  C = Dir$:  Wend
        ReDim V(1 To L + 1, 1 To L + 1)
        V(L + 1, L + 1) = 0
   With Application
       .StatusBar = "       Processing " & L & " csv files …"
        F = FreeFile
        C = Dir$(P & "*.csv")
    Do
        K = 0
        N = N + 1
        V(N, N) = 0
        Open P & C For Input As #F
        S = Split(Input(LOF(F), #F), vbLf)
        Close #F
    For R = 4 To .Min(.Match("""#*", S, 0) - 5, 3 + L * 2) Step 2
        K = K + 1
        V(K + N, N) = Round(Val(Split(Split(S(R), ",")(4), """")(1)), 2)
        V(N, K + N) = V(K + N, N)
    Next
        L = L - 1
        C = Dir$:  If N Mod 300 = 0 Then DoEvents
    Loop Until C = ""
       .SheetsInNewWorkbook = 1
       .StatusBar = False
        Workbooks.Add.Sheets(1).[A1].Resize(N + 1, N + 1).Value2 = V
       .DisplayAlerts = False
        ActiveWorkbook.SaveAs P & "CSV Import ", 51
       .DisplayAlerts = True
   End With
End Sub

Superb! It worked perfectly!! This code did the job as accurate as no other!

All those tasks in a single code? I give you the accolades! The beauty of it all is that it could even be applied to another set of csv text files with different row numbers different from the ones I uploaded. This is pure professionalism. Thank you.

 

[Ay Sticky]

As it works a treat on my side just see the codeline where the statement Beep is in order to understand your issue :​

you just forgot to update the path within the code … Should I remind « Coding can't be guessing ! »​

​

The better explanation & attachment, the better code !​

As you said, I got the file path wrong. It worked swiftly after adjusting to the appropriate file path.... Still looks like day dream to me. The manual process could have cost me months. Thank you once again. I guess you are the owner of this platform, looking from your high knowledge in vba coding and the patience you possess in helping people solve their excel problems.

 

[Marc L]

I guess you are the owner of this platform, looking from your high knowledge in vba coding

Very not ! I Just know some Excel basics like some VBA basics as well …​