Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of bfuchs

ASKER

Avatar of bfuchs

ASKER

@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
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.
Avatar of bfuchs

ASKER

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
Avatar of bfuchs

ASKER

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
Avatar of bfuchs

ASKER

Thank you!
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.
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.
Avatar of bfuchs

ASKER

@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
Avatar of bfuchs

ASKER

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
Avatar of bfuchs

ASKER

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
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.