How to extract last few characters in a file name

Posted on 2010-01-03
Last Modified: 2012-05-08
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!
Question by:ltdanp22
    LVL 17

    Accepted Solution

    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)
    LVL 17

    Expert Comment

    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)
    LVL 6

    Assisted Solution

    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)

    Expert Comment

    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.

    Author Comment

    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?



    Expert Comment

    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

    LVL 17

    Expert Comment

    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
    LVL 17

    Expert Comment

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

    Expert Comment

    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.  
    LVL 17

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now