Remove timestamp from file name

Hi Experts,

I'm looking to create a VBA function that will accept a file name as a string parameter, then rename a file as follows.
if parameter is "Skilled_Nursing_Visit_Note" then will look at given folder for file starting with that name for example "Skilled_Nursing_Visit_Note_2018-Nov-26_1901.csv"
and rename that file to just "Skilled_Nursing_Visit_Note.csv", in other words, remove the date/time and sequence timestamp from that file name.

Thanks
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
This assumes the files are all CSV and that there are not multiple files starting with the same text...

Sub RenameFile(strName As String)

    Const FolderPath As String = "C:\Documents\" 'modify path to suit
    Dim var As String
    var = Dir(FolderPath & strName & "*")
    
    If var <> "" Then
        Name FolderPath & var As FolderPath & strName & ".csv"
    End If
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
As always the question is why?  What problem are you trying to solve?  When you ask the question this way, you have determined what you think is the best solution.  You just don't know how to implement it.  You are just asking someone to load the bullets into your gun.  However, renaming the file, may in fact NOT be the best solution.
bfuchsAuthor Commented:
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

bfuchsAuthor Commented:
@Wayne Taylor,
Forgot to mention, should only rename files of the same type (csv)
while testing yours I realized it was about to rename a .mdb file...
Thanks,
Ben
PatHartmanCommented:
You seem to have multiple active threads on this topic.  The one you linked to is about conversion of data types.  This one is asking to rename the input file.  WHY would you ever want to rename the input file?  WHY can you not use the file name as is?  WHAT is it that makes you think that you need to rename the file?  I'm going to guess that you might be trying to use the newer, dumber import method where the file name always remains fixed and you can't change it at run time.
bfuchsAuthor Commented:
WHY would you ever want to rename the input file?  WHY can you not use the file name as is?  WHAT is it that makes you think that you need to rename the file?
As mentioned in the other thread, I have a linked table to Excel/CSV file named C:\Application\Skilled_Nursing_Visit_Note.CSV
When we want download a newer file from the web, it always comes with a time stamp, therefore rename it is a must.
Actually if you read that entire comment, you will see I'm currently using similar approach anyway, just for a single file (the latest downloaded file), however since I'm forced to use this now to multiple files, therefore I'm looking for a function to facilitate the process...

Thanks,
Ben
bfuchsAuthor Commented:
Actually I got it to work as follows.

Public Sub RenameFile(strName As String, sFolder As String, sExt As String)

    'Const FolderPath As String = "C:\Application\" 'modify path to suit
    Dim var As String
    var = Dir(sFolder & strName & "*." & sExt)
    
    If var <> "" Then
        Name sFolder & var As sFolder & strName & ".csv"
    End If
    
End Sub

Open in new window


Thanks,
Ben
bfuchsAuthor Commented:
Thank you!
Fabrice LambertConsultingCommented:
The Dir() function is a relic of the past, and have reliability issues.
Let's be modern and use the FSO object instead:
Public Sub RenameFile(ByVal fileName As String, ByVal folderPath As Atring, ByVal fileExt As String)
    Dim fso As Object        '// Scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder As Object        '// Scripting.Folder
    Set folder = fso.GetFolder(folderPath)

    Dim file As Object        '// Scripting.File
    For Each file In folder.Files
        If(file.Name Like fileName & "*." & fileExt) Then
            file.Move folderPath & fileName & ".csv"
        End If
    Next

Open in new window


Also, give up hungarian notation, it provides nothing usefull.
PatHartmanCommented:
therefore rename it is a must.
Actually, it is not.  

When you link to a file with a name like that, the best option is to rename the file in Access to standardize the name.  So
myfile_20191130.xlsx should be named "myfile" when you link it.  All your queries and everything else refers to it as myfile even though the physical file name is something else.  Whenever you want to replace the old file with a new one,
1. delete the previous link
2. link to the new version of the spreadsheet using the TransferSpreadsheet method.
3. name the table "myfile"

That leaves the file with its original name but allows you to have a standard, fixed name in the Access app.  You will of course need code to read the directory and figure out what the newest file name is so you can relink it with the correct name.  I don't know how this is automated but typically when files get pushed to me, they go into a specific folder.  The app processes each file in the folder, one at a time.  After a file is processed it is moved to the processed folder.  That is the code I would build.  I would not ever rename the file unless I had to.  I did have a situation where some bright lights sent me files with two periods and of course Access chokes completely so those I had to rename to change all but one of the dots to an underscore but that was because the other people couldn't make a filename using standard windows naming conventions.  But changing a dot to an underscore doesn't change the innate identifying nature of file names with dates.
bfuchsAuthor Commented:
@Fabrice,

Actually I need to check first for existence of file, so the original code looks like the following.
Public Sub RemoveTimeStampFromFile(strName As String, sFolder As String, sExt As String)

    'Const FolderPath As String = "C:\Application\" 'modify path to suit
    Dim var As String
    If Dir(sFolder & strName & "." & sExt) <> "" Then
        Kill sFolder & strName & "." & sExt
    End If

    var = Dir(sFolder & strName & "*." & sExt)
    
    
    If var <> "" Then
        Name sFolder & var As sFolder & strName & ".csv"
    End If
    
End Sub

Open in new window

How do you accomplish that w/o the use of dir()?

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,
I would need one modification for this function, instead of replacing any file it finds (and they may be multiple files), I would need it to do for the latest file created only, while dir() function does the opposite, it returns the first file matching criteria.
Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

Looks like I managed to do it as follows.

Public Sub RemoveTimeStampFromFile(strName As String, sFolder As String, sExt As String)

    'Const FolderPath As String = "C:\Application\" 'modify path to suit
    Dim var As String, var1 As String

    var = Dir(sFolder & strName & "*." & sExt)
    Do While Len(var) > 0
        var1 = var
        var = Dir
    Loop
    If Dir(sFolder & strName & "." & sExt) <> "" Then
        Kill sFolder & strName & "." & sExt
    End If
    Name sFolder & var1 As sFolder & strName & ".csv"
    
End Sub

Open in new window


Let me know if you have a better way of accomplishing it.

Thanks,
Ben
PatHartmanCommented:
Let me know if you have a better way of accomplishing it.
I gave you an option that doesn't require renaming the file.  I think that IS better.  When you import files this way, it is always good to keep a log of EXACTLY what file was imported and when.  In fact, I log the file name first and that allows me to append a batchID for each record so I know exactly which record came from which file.  Just in case.  Renaming the file accomplishes nothing except to obscure your import trail.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.