Change all file names in folder based on cell values

The following is working for me ... what's your error and what line does the error occur on ?

Please Login or Register  to view this content.

Anything else you'd like to tweak ??
... i'd appreicate it if you'd click and leave a comment .

Originally Posted by nimrod1313

Anything else you'd like to tweak ??
... i'd appreicate it if you'd click and leave a comment .

Well, Nimrod, I feel bad for asking again, but, how would you add the current month at the beggining of the file name?.

Please Login or Register  to view this content.

NOTE: If this has helped you please click on "add reputation" and leave a comment. thx

month and year

Please Login or Register  to view this content.

Originally Posted by bartolos

Well, Nimrod, I feel bad for asking again, but, how would you add the current month at the beggining of the file name?.

You need to try many function if you can.

Please Login or Register  to view this content.

insert this line inside the loop, you know where to put?

It works like a charm, month and year first, then the file name, THANK YOU.

As you said, Nimrod, it works in folders containing only excel files, otherwise, it breaks.

Here's a version that will allow you to look for xls* files in a folder with various files and only work on files that are excel

Please Login or Register  to view this content.

Cool. That?s right, an if then statement to run the following functions only when the file ends with xls.

In case I wanted to put the value of another cell into a different variable newFileName0, so the expected file name was the combination of newFileName0 & "_" & newFileName

would the following be correct? (I know it is not, I just wanted to show I?ve tried

Public Sub ChangeFileNames()
Dim oFSO As Object
Dim newFileName As String
Dim newFileName0 As String
Dim fil

Const PATH_TO_FOLDER As String = "C:\Users\glendon\Documents\EXCEL\testDemo"
Const SOURCE_SHEET_NAME = "Sheet1"

Set oFSO = CreateObject("Scripting.FileSystemObject")
For Each fil In oFSO.GetFolder(PATH_TO_FOLDER).Files
If fil.Name Like "*.xls*" Then
newFileName = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 1, 1) & "." & Split(fil.Path, ".")(1)
newFileName = Replace(newFileName, ":", "_")
newFileName = Replace(newFileName, " ", "_")
newFileName = Replace(newFileName, "-", "_")
newFileName = Format(Now(), "Mmm") & "_" & newFileName
newFileName0 = GetInfoFromClosedBook(PATH_TO_FOLDER, fil.Name, SOURCE_SHEET_NAME, 5, 1) & "." & Split(fil.Path, ".")(1)
newFileName0 = newFileName0 & "_" & newFileName


oFSO.moveFile fil.Path, PATH_TO_FOLDER & newFileName0
End If
Next fil
End Sub

Public Function GetInfoFromClosedBook(Path, FileName, SheetName, RowNum, ColNum)
GetInfoFromClosedBook = ExecuteExcel4Macro("'" & Path & "[" & FileName & "]" & SheetName & "'!R" & RowNum & "C" & ColNum)
End Function

close ... but Your code will have the file extention added twice to the filename

Try this ... (note untested)

Please Login or Register  to view this content.

I've added a temporary msgbox that will let you see what your file name will be ... this should help you work out the kinks in your file naming tweaks

Please Login or Register  to view this content.

To help you with your "folder path" I'm giving you two new versions/examples for you to examine

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I've removed the requested info from my post ... please verify .

I've also posted two new alternatives to setting up the source folder path.

Cheers
Nimrod