bfuchs
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_Not e" then will look at given folder for file starting with that name for example "Skilled_Nursing_Visit_Not e_2018-Nov -26_1901.c sv"
and rename that file to just "Skilled_Nursing_Visit_Not e.csv", in other words, remove the date/time and sequence timestamp from that file name.
Thanks
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_Not
and rename that file to just "Skilled_Nursing_Visit_Not
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
@Pat,
Thanks,
Ben
What problem are you trying to solve?https://www.experts-exchange.com/questions/29127720/Mapping-Excel-fields-when-linking-to-Access-app.html?anchorAnswerId=42745715#a42745715
Thanks,
Ben
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
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.
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_Nur
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
ASKER
Actually I got it to work as follows.
Thanks,
Ben
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
Thanks,
Ben
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:
Also, give up hungarian notation, it provides nothing usefull.
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
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.
ASKER
@Fabrice,
Actually I need to check first for existence of file, so the original code looks like the following.
Thanks,
Ben
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
How do you accomplish that w/o the use of dir()?Thanks,
Ben
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
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
ASKER
Hi Experts,
Looks like I managed to do it as follows.
Let me know if you have a better way of accomplishing it.
Thanks,
Ben
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
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.