How to get new list of file names?

[Jake975]

I have a macro that is renaming a group of files based on a given name. Im trying to get the new list of new file paths created to have a macro create a new CSV file with them for use in another application. here is the code im using

Sub Rename_Files()
 
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Home")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
 
Dim new_name As String
 
Set fo = fso.GetFolder(sh.Range("H1").Value)
 
For Each f In fo.Files
      new_name = Application.VLookup(f.Name, sh.Range("A:B"), 2, 0)
      If new_name = "" Then 'do nothing
      Else
      f.Name = new_name
      End If
Next

any help would be grateful thanks in advance

 

[alansidman]

If you want a list of files in a particular folder then this may work for you

Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

 

[Jake975]

Thank you I got that to pull the file path and list them like i needed. it was a huge help