rename csv file by removing date/time stamp before importing

[fishy]

I have several files that I import every day but I have to manually rename
them by removing the date/time stamp from the name i.e.

ICM_AMS_MS_Call_Stats_01011901000000.csv

has to become ICM_AMS_MS_Call_Stats.csv due to my import script.

These are saved to the same filepath every day so I need some function that
will rename the files to trim the datestamp before I call the import.

Any help would be appreciated

 

[Douglas J. Steele]

Try:

Dim lngLastUnderscore As Long
Dim strNewName As String

lngLastUnderscore = InStrRev(strName, "_")
If lngLastUnderscore > 0 Then
strNewName = Left(strName, lngLastUnderscore - 1) & ".csv"
Else
strNewName = strName
End If

 

[Stuart McCall]

I have several files that I import every day but I have to manually rename
them by removing the date/time stamp from the name i.e.

ICM_AMS_MS_Call_Stats_01011901000000.csv

has to become ICM_AMS_MS_Call_Stats.csv due to my import script.

These are saved to the same filepath every day so I need some function
that
will rename the files to trim the datestamp before I call the import.

Any help would be appreciated

Dim strOldName As String, strNewName As String
Dim strPath As String

strPath = "C:\Temp\" 'Change this to the correct path

strOldName = strPath & "ICM_AMS_MS_Call_Stats_01011901000000.csv"
strNewName = strPath & Left$(strOldName, InstrRev(strOldName, "_") - 1) &
".csv"

Name strOldName As strNewName

(then import from strNewName, of course)

 

[fishy]

Apologies, the date/time stamp is of the format

ICM_AMS_MS_Call_Stats_YYYY-MM-DD_HH-MM-SS.csv

This therefore means that I am getting a bad filename error.

I thought I could get away with changing this to wildcards ie

ICM_AMS_MS_Call_Stats_****-**-**_**-**-**.csv

But this doesnt work.

Any ideas?

 

[Stuart McCall]

Apologies, the date/time stamp is of the format

ICM_AMS_MS_Call_Stats_YYYY-MM-DD_HH-MM-SS.csv

This therefore means that I am getting a bad filename error.

I thought I could get away with changing this to wildcards ie

ICM_AMS_MS_Call_Stats_****-**-**_**-**-**.csv

But this doesnt work.

Any ideas?

Is the part "ICM_AMS_MS_Call_Stats" always the same? If not, does it ever
contain a number?

 

[fishy]

Yes, this remains constant, only the date then time is variable after the
underscore.

R

 

[Stuart McCall]

Yes, this remains constant, only the date then time is variable after the
underscore.

R

No problem, then.

Dim strOldName As String, strNewName As String
Dim strPath As String, i As Long

strPath = "C:\Temp\" 'Change this to the correct path
strOldName = "ICM_AMS_MS_Call_Stats_01011901000000.csv"

For i = 1 To Len(strOldName)
If IsNumeric(Mid$(strOldName, i, 1)) Then
Exit For
End If
Next
strNewName = Left$(strOldName, i - 2)

Kill strNewName
Name strPath & strOldName As strPath & strNewName

What the above code does is to look down the string till it finds a numeric
character, then assigns the leftmost part of strOldName (minus the numeric
and the underscore) to strNewName.

Then it deletes ICM_AMS_MS_Call_Stats because it'll always be the same name.
Then it renames the file as you require.

The above is untested because I have to rush out. Hope it helps you.

 

[fishy]

This is debugging at the kill function as its trying to kill the new named
file.