How to extract last few characters in a file name

Or you can do it with another method.

You have .accdb extension. It's length is 6, So first of all get rid of it:

Dim Extracted as String
Extracted = Mid(YourString, 1, Len(YourString) - 6)
Extracted = Mid(Extracted, 12)

It depends a little on whether the location of the date in the filename is in a fixed position or not. If all your filenames start with "ISCM Output ", then it's easy.

Thanks for all the replies guys.

DateTime stamp are always in the same position so it is pretty easy. Just need to know the syntax.

What is the syntax for getting the filename from the currently open file (the one the VBA code is running in)?

What exactly does #yyyy:mm:dd hh:nn:ss# do? Take the string "2010-01-03 00-09-45" and turn it into "2010:01:03 00:09:45"? Are colons allowed in file names?

Thanks!

Dan

Try this to extract the date and time strings from your file name.  Assuming the date and time are always separated by a space and you have only one period (".") in the file name, this should work regardless of what your filename starts with:

Dim str_Date As String
        Dim str_File_Base As String
        Dim str_File_Ext As String
        Dim str_Time As String

        str_File_Base = "ISCM Output 2010-01-03 00-09-45.accdb"

        str_File_Ext = str_File_Base.Substring(str_File_Base.IndexOf(".") + 1)      ' Get file extension.

        str_File_Base = str_File_Base.Substring(0, str_File_Base.IndexOf("."))      ' Eliminate file name extension.

        str_Time = str_File_Base.Substring(str_File_Base.LastIndexOf(" ") + 1)      ' Assume time starts at last " " in
        Console.WriteLine("Time: " & str_Time)                                      '   file name.

        str_File_Base = str_File_Base.Substring(0, str_File_Base.LastIndexOf(" "))  ' Eliminate time in file name.

        str_Date = str_File_Base.Substring(str_File_Base.LastIndexOf(" ") + 1)      ' Assume date starts at last " " in
        Console.WriteLine("Date: " & str_Date)                                      '   remainder of file name.

        str_File_Base = str_File_Base.Substring(0, str_File_Base.LastIndexOf(" "))  ' Eliminate date in file name =>
        Console.WriteLine("File Base: " & str_File_Base)                            '    file base name.
        Console.WriteLine("File Ext: " & str_File_Ext)

Select allOpen in new window

Hey guys... It's not VB.NET, It's VB6

and he already got the answer... First two codes I sent works... That's all

@ltdanp22, have you ever seen my comments? First two comments is mine and code just works in VBA

CSecurity -

My sincerest apologies for not recognizing your detailed responses to Itdanp22.

However, based on Itdanp22's questions almost an hour after your responses, it sounds like you didn't fully address his question.  Hence, my input (for what it's worth).  It doesn't sound like he got the answer he was looking for, contrary to your humble opinion.

Forgive my ignorance - what was the key that told you Itdanp22's question was VB6?   All I saw was "Visual Basic Programming" for the zone.

Maybe a little less arrogance and a little more compassion when answering questions.  

He said:
What is the syntax for getting the filename from the currently open file (the one the VBA code is running in)?

It means VBA. VBA and VB6 have no diff in style. Your code is for VB.NET

He got the answer... Code I provided works and that's all