change multiple filenames in directory to cell values

[james potter]

I'm wondering if there is a way to automatically change multiples filenames is a certain directory with the help of cell values in excel.

I recieve multiple files from different sources and need them to change to a standard format. The sender is unable to do that for me.

I think of the following scenario. Button to import the finenames of a specified diretory in an excelsheet. Columns next to filename will be used to create the new filename, combonation of office number and date. I already create the code to import filenames in an excelsheet. So I only need a vba code that will lookup the filename presented in cell A2 and change it according to cell values presented in cell B2,B3 & B4. The ext part does not need to be changed. I'm using excel 2007.

Is this possible to do in excel/vba?

 

[golfbum71]

Copy Files

james potter,

Below is what I think you asked for:

Sub CopyFiles()

Dim SourceFile, DestinationFile
SourceFile = Range("A2")     ' The file to be copied is in Column A.
DestinationFile = Range("B2") & Range("B3") & Range("B4")  ' The new file location and new file name.
MsgBox "Source: " & Chr(10) & SourceFile & Chr(10) & "Destination: " & DestinationFile
'Uncomment Line Below when you know it's working
' FileCopy SourceFile, DestinationFile    ' Copies files from source to target.
End Sub

Now, if you have a lot of files and can put each one on its own line with the old file name in A and the new file name in E, then this will work on a batch of them...

Sub CopyManyFiles()
'Below is for doing many files at the same time

    Dim fd As FileDialog
    Dim folderstring As String
    
'''' Below is for choosing a folder to copy to if you need it ...
'    MsgBox "In the next dialogue box, please choose the folder you'd like the photos *copied* to..."
'    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'        With fd
'        .Show
'    End With
'    folderstring = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

'The Old file is in column A, The Destination is in Column E
For x = 2 To Range("A65000").End(xlUp).Row
SourceFile = Range("A" & x)     ' The file to be copied is in Column A.
DestinationFile = Range("E" & x)   ' The new file location and new file name.

'Uncomment Line Below when you know it's working
' FileCopy SourceFile, DestinationFile    ' Copies files from source to target.

''Below is for testing...shows name of each file...it gets old with lots of files,
''but you can check your work before moving your files
MsgBox "Source: " & Chr(10) & SourceFile & Chr(10) & "Destination: " & DestinationFile

Next x


End Sub

I'm sorry if it's a little confusing. Hopefully you'll be able to adapt it to what you want.

Cheers,
Geoff