PDA

View Full Version : Finding and renaming files from spreadsheet list



Stanni
09-15-2015, 04:38 AM
Hi All, I'm hoping someone can help me with a bit of a brain teaser!

I have a single folder with circa 60k pdf files in, all named with a random hash. I have an index on a spread sheet of what is in each of the files with the corresponding file name, but I'm hoping there is someway of looping through either the folder or the spread sheet and simultaneously renaming and relocating the file. I have attached a sample of the spread sheet I have. What I would like to do is rename the filename in column B to the name in column C, and save it in a subdirectory named the same as column D. I have used VBA to rename and relocate files before, but never combined with actually looking up the filename to find the target name and location from list. My question is two fold: is it more efficient/easier to loop through each file in the directory and lookup the target info from the list, or loop through the rows on the spread sheet and search for the file (my guess is the former?!?), and secondly, how would it be done? I'd love to sit and work it out, but I'm against the clock!

Thanks, Chris

SamT
09-15-2015, 02:56 PM
Sub FastAndDirty_MoveFiles_OneTime()

Const OldDir As String = "single folder with circa 60k pdf files in\" 'Folder name with trailing back slash
Dim Cel As Range

For Each Cel In Range("B1:B999") 'edit "999" to LastRow in B)
With Cel
Name OldDir & .Value As .Offset(0, 2).Value & "\" & .Offset(0, 1).Value
End With
Next Cel
End Sub