• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

How to extract last few characters in a file name

I have a file that has a date time stamp in its file name:

ISCM Output 2010-01-03 00-09-45.accdb

I need to pull the date time stamp from this file name. Can someone tell me how to do this?

Thanks in advance!
2 Solutions
TimeDate stamp have 19 chars.

First chars before it have 12 chars.

So to extract time/date:

Dim Extracted as String
Extracted = Mid(YourString, 12, 19)
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)
use currentProject.Name to get the name of the access file.

Dim timestamp as Date
timestamp = #yyyy:mm:dd hh:nn:ss#
to convert the string to a timestamp (where yyyy are the 4 characters corresponding to the year etc)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.
ltdanp22Author Commented:
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?


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)

Open 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now