1

I have thousands of .csv files with specific file names. Each .csv files have the 1st row as header, and the following rows (usually 20 rows) as data. I need help in copying the filename of each .csv file into the last column for 20 rows, and would need to cycle through all the .csv files in the folder.

Thanks in advance!

WindWaterWinter
  • 11
  • 1
  • 3
  • 1
    Start here: https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba?rq=1 – Tim Williams Nov 21 '16 at 02:59
  • Hi, I saw the code for loop. http://stackoverflow.com/a/10380381/5750419 but not sure how and where to input the codes for doing the copying of the filename. – WindWaterWinter Nov 21 '16 at 04:13

1 Answers1

3

This should do it. Depending on what your doing you may want to alter it so it wont add the filename field if it already exists. etc.

Public Sub UpdateFiles()

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim xl As New Excel.Application
    Dim wb As New Excel.Workbook
    Dim lastRow
    Dim lastColumn

    Dim folder

    'Change this folder to the one you want.
    Set folder = fso.getFolder("c:\notbackedup\")

    'Uncomment to watch/debug code
    'xl.Visible = True

    For Each f In folder.Files
        Debug.Print f.Name
        If (f.Name Like "*.csv") Then
            Set wb = xl.Workbooks.Open(f.Path)
            lastRow = wb.Sheets(1).UsedRange.Rows.Count
            lastColumn = wb.Sheets(1).UsedRange.Columns.Count
            wb.Sheets(1).Range(ColumnLetter(lastColumn + 1) & "1").Value = "FileName"
            wb.Sheets(1).Range(ColumnLetter(lastColumn + 1) & "2:" & ColumnLetter(lastColumn+1) & lastRow).Value = f.Name
            wb.Save
            wb.Close True
        End If
    Next
    xl.Quit


End Sub
Function ColumnLetter(ByVal ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function
ClintB
  • 509
  • 3
  • 6