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

[chrisd2000]

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

 

[John_w]

Can you add some more code which would give me a prompt to enter 7 more characters to put right before .xls?

Use the InputBox function.

PS please use CODE tags.

 

[chrisd2000]

I posted the same question to Reddit at Can a macro rename Excel files based on a cellname? and got some good code from Redditor johndoe555 that worked great but then another tweek occured to me. Can you add some more code which would give me a prompt to enter 9 more characters to put both right before .xls and in cell A1 and also copy the font formatting from cell B1 to cell A1?

 

[chrisd2000]

My IT guy says I have to know what macro code does before I can use it so that I am not running malicious code. I got someone to alter it so that I can put in the corporate calendar week into the filename. So I am trying to Google various parts of the code and I have found some useful explanations. Can you fill in the gaps? Here is what I found so far:

With Application.FileDialog(msoFileDialogFolderPicker)

selects a folder from a certain path and import all the files inside it http://stackoverflow.com/questions/1...cker-using-vba

filepath = .SelectedItems(1)

This part is involved in putting the filepath of the selected file in the variable filepath - See more at: http://lroedal.net/en/vba/read-from-....Od11itRI.dpuf or VBA: How to read from a text file to a string | lroedal.net

Set wb = Workbooks.Open

- The line that starts with this text uses the file path you selected in FileDialog - http://stackoverflow.com/questions/2...-manipulate-it

Sub RenameAllExcelFilesInFolderWInput()
With Application.FileDialog(msoFileDialogFolderPicker)     
[/INDENT]
<code>.Show     
filepath = .SelectedItems(1) 
End With  
Set r = Workbooks.Add.Worksheets(1).Range("A1") 
StrFile = Dir(filepath & "\*.*") 
sUser = InputBox("Enter Seven More Characters")  
Do While Len(StrFile) > 0     
strExtension = Split(StrFile, ".")(UBound(Split(StrFile, ".")))      
Set wb = Workbooks.Open(filepath & "\" & StrFile)     
StrNewfullfilename = wb.Sheets(1).Range("B1").Value & sUser & "." & 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 </code>  End Sub