VBA for Inserting Columns

[haa]

Hi all, I'm trying to do the following and need a VBA code than can cut down the tediousness and time it takes to complete this manually and 1 by 1

I have a folder with 40 csv files. For each file, I want to
1) Replace column A title with 'OO Route'
2) Fill the contents of that column with the name of the file

How can I do that at once for all 40 csv files?

 

[John_w]

This macro should be the safest, but not the fastest, method. Change the csvFolder path in the code and test it on a subset of the .csv files in a 'test' folder.

Public Sub Modify_CSV_Files()

    Dim FSO As Object
    Dim FStext As Object
    Dim FSfile As Object
    Dim csvFolder As String
    Dim csvLines As Variant, p As Long, i As Long
        
    csvFolder = "C:\path\to\csv folder\"  '<---- CHANGE THIS
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    For Each FSfile In FSO.GetFolder(csvFolder).Files
        If LCase(FSfile.Name) Like "*.csv" Then
            Set FStext = FSO.OpenTextFile(FSfile.Path)
            csvLines = Split(FStext.ReadAll, vbCrLf)
            FStext.Close
            p = InStr(csvLines(0), ",")
            csvLines(0) = "OO Route" & Mid(csvLines(0), p)
            For i = 1 To UBound(csvLines) - 1
                p = InStr(csvLines(i), ",")
                csvLines(i) = Left(FSfile.Name, InStrRev(FSfile.Name, ".") - 1) & Mid(csvLines(i), p)
            Next
            Set FStext = FSO.CreateTextFile(FSfile.Path)
            FStext.Write Join(csvLines, vbCrLf)
            FStext.Close
        End If
    Next
    
End Sub

A much faster method would be to open each .csv file using Workbooks.Open and change the values in column A (no looping required). However if the .csv files contain date strings, or strings that Excel interprets as dates, it may change the format of those strings in the output .csv.