Inserting Date Last Modified in lots of excel files without opening

[Ceag]

I asked as it worked form me when I downloaded the files and placed them in a folder.

Have you kept the \ on the end of the path?

I would say that the path being wrong seems to be the issue. Make sure 100% that the path leads to the folder in question.

You can copy the path from the code and paste it into the file explorer and see if it takes you to the folder or not.

Code is superb. Path is ok, it seems that code stops when finds a file that does not have appropriate value in target cells.

[Ceag]

Code is working properly providing files are correct (appropriate values in targeting cells). When a file with wrong values is open, it stops.
Thanks, thanks, thanks!

I had to create some test data som they all have the same Modified Date

Option Explicit


Const sPath As String = "C:\Users\Daddy\Desktop\Test\"  '   <<<<<<<<<<<<<<<<<<<<< Change


Sub RenameFiles()
    Dim oFSO As Object, oFolder As Object, oFiles As Object, oFile As Object
    Dim sName As String, sBirth As String, sMod As String
    Dim wb As Workbook
    Dim vName As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.Getfolder(sPath)
    
    For Each oFile In oFolder.Files
        If oFSO.GetExtensionName(oFile.Name) <> "xlsx" Then GoTo GetNext
        
        sMod = Format(oFile.DateLastModified, "ddmmyy")
                
        Set wb = Workbooks.Open(sPath & oFile.Name)
        
        sName = UCase(wb.Worksheets("Sheet2").Range("AG2"))
        vName = Split(sName, " ")
        sName = Left(vName(1), 3) & Left(vName(0), 3)
        
        sBirth = Format(wb.Worksheets("Sheet2").Range("AI2").Value, "ddmmyy")
        
        wb.Close False
        
        Call oFSO.MoveFile(sPath & oFile.Name, sPath & sName & sBirth & "." & sMod & "." & oFSO.GetExtensionName(oFile.Name))


GetNext:
    Next


    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

[Ceag]

Paul_Hossler is king of VB.

[Paul_Hossler]

<blush>

Without seeing how the data could go wrong, I could only guess at some of the likely failures to add a check

Option Explicit


Const sPath As String = "C:\Users\Daddy\Desktop\Test\"  '   <<<<<<<<<<<<<<<<<<<<< Change


Sub RenameFiles()
    Dim oFSO As Object, oFolder As Object, oFiles As Object, oFile As Object
    Dim sName As String, sBirth As String, sMod As String
    Dim wb As Workbook
    Dim vName As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.Getfolder(sPath)
    
    For Each oFile In oFolder.Files
        If oFSO.GetExtensionName(oFile.Name) <> "xlsx" Then GoTo GetNext
        If oFile.DateLastModified = 0 Then GoTo GetNext
        If Not IsDate(oFile.DateLastModified) Then GoTo GetNext
        sMod = Format(oFile.DateLastModified, "ddmmyy")
                
        Set wb = Workbooks.Open(sPath & oFile.Name)
        
        sName = UCase(wb.Worksheets("Sheet2").Range("AG2"))
        If Len(sName) = 0 Then GoTo GetNext
        vName = Split(sName, " ")
        If UBound(vName) <> 1 Then GoTo GetNext
        sName = Left(vName(1), 3) & Left(vName(0), 3)
        
        If Len(wb.Worksheets("Sheet2").Range("AI2").Value) = 0 Then GoTo GetNext
        If Not IsDate(wb.Worksheets("Sheet2").Range("AI2").Value) Then GoTo GetNext
        sBirth = Format(wb.Worksheets("Sheet2").Range("AI2").Value, "ddmmyy")
        
        wb.Close False
        
        Call oFSO.MoveFile(sPath & oFile.Name, sPath & sName & sBirth & "." & sMod & "." & oFSO.GetExtensionName(oFile.Name))


GetNext:
    Next


    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

[Ceag]

One more thing. I need an advice. Now I have thousands of files with names consisted of six capital letters, six numbers, dot, six numbers, dot, xlsx (I.E. SMIJOH140469.040823). What I would like to do is to have all of them sorted in subfolders made according to first twelve symbols (I.E. SIMJOH140469), AND I would like ALL the files starting with same twelve symbols to end-up in the same subfolder. (I.E. SMIJOH140469.040823, SMIJOH140469.080223, SMIJOH140469.030922 to folder SMIJOH140469.
Can it be done?