View Single Post
 
Old 06-26-2014, 02:59 PM
chrisd2000 chrisd2000 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Jun 2014
Posts: 16
chrisd2000 is on a distinguished road
Default Can anyone here tweek this macro for renaming Excel files based on a cell's contents?

Can anyone here tweek this macro for renaming Excel files based on a cell's contents?

I use it to rename all the files in a folder based on the text that exists in cell B1.
Can you add some more code which would give me a prompt to enter 7 more characters to put right before .xls?

Here is the existing VBA code.

Sub RenameAllExcelFilesInDirectory()

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
filepath = .SelectedItems(1)
End With

Set r = Workbooks.Add.Worksheets(1).Range("A1")
StrFile = Dir(filepath & "\*.*")

Do While Len(StrFile) > 0
strExtension = Split(StrFile, ".")(UBound(Split(StrFile, ".")))

Set wb = Workbooks.Open(filepath & "\" & StrFile)
StrNewfullfilename = wb.Sheets(1).Range("B1").Value & "." & strExtension
wb.Close

r.Value = StrFile 'print old name
r.Offset(, 1).Value = StrNewfullfilename 'print new name
Set r = r.Offset(1)
Name filepath & "\" & StrFile As filepath & "\" & StrNewfullfilename
StrFile = Dir
Loop

End Sub
Reply With Quote