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!
Who is Participating?
CSecurityConnect With a Mentor Commented:
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)
iandianConnect With a Mentor Commented:
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)
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.