asked on

Excel VBA: add datestamp string of files located in a directory

Hello experts,

I am looking for a procedure to cover the following requirement:

1-Read range A1 which contains directory
2-Add at the end of the various files located in reported directory datestamp ie : & Format$(Date, "yyyymmdd") _
& Format(Now, "hhmmss")

Checking:
if A1 doesn't contain a directory string exit sub with msgbox: "A1 doesn't contain a directory string"

If you have questions, please contact me.
Thank you in advance for your help.

Daniel Pineault

Look over the attached. To run the code, run the macro named DateStampDir. I suggested you test it out thoroughly.
DateTimeStampFileInFolder.xlsm

Luis Diaz

ASKER

Thank you for this proposal.
I prefer to simplify and have a unique procedure as it will be included to my personal.xlsb.
This is the one used for create datestamp folder:

Sub Create_Date_Stamp_Folder()
    Dim sFolder As String
    
    'This assumes that the root folder is listed in A1 of the Active Sheet
    'If this is not the case, declare a Sheet Variable and set it accordingly and then qualify the range with the Sheet Refernce
    If Range("A1").Value = "" Then
        MsgBox "You haven't input the Report Folder in the cell A1.", vbExclamation
        Exit Sub
    End If
    sFolder = Range("A1").Value
    'Checking if the Root Folder doesn't exist.
    If Len(Dir(sFolder, vbDirectory)) = 0 Then
        MsgBox "The Report Folder doesn't exists.", vbExclamation, "Action Cancelled!"
        Exit Sub
    End If
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    'Creating New Folder with DateTimeStamp inside the Root Folder
    sFolder = sFolder & Format(Now, "YYYYMM_DDHHMMSS")
    MkDir sFolder
    MsgBox "Report Folder " & sFolder & " has been created successfully.", vbInformation, "Report Folder Created!"
    Shell "C:\WINDOWS\explorer.exe """ & sFolder & "", vbNormalFocus
End Sub

Open in new window

Regards,

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Pineault

Then copy in the function to make a single proc, but realistically, you are MUCH better served keeping the procs as they are, then you can use them, with a single line, in any other proc or your choosing. That said, everything you need was provided, it's up to you on how you implement it at your end of the world.

Luis Diaz

ASKER

Thank you all.
I will test them soon.

Luis Diaz

ASKER

I tested and they works. Thank you again for your help!