Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Strip filename from path?

    Hi there!

    I'm quite rusty with my Access skills but have been summoned to make some modifications to a friend's database...

    He has an art database that captures info about the piece of art. Each record has a field, [image], populated with a path/filename that stores the location of the image.

    What I am trying to do is capture the name of the file from [image] (not the full path, just the name) and copy it to the [Name] field. My friend has already gone through the filesystem and named all the images with their proper names, and when I told him that he would have to re-enter the names in the database he wanted to shoot me!

    So, if the [image] field contains C:\users\jim\photos\art\image1.jpg, my goal would be to scrape the word "image1" and plop it into the [name] field.

    Any recommendations on a quick n' easy way to do this?

    THANKS!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Since I already have the code...

    Put this in a module and you can call it to get the filename:

    Code:
    Public Function GetFilenameFromPath(strFilenamePath As String)
    
    'Description :      This function will separate the name of the file, given the path and filename
    '
    'Parameters :       strFilenamePath     The path and filename of the file
    '
    'Return :           String.             The name of the file.
    '
    '
    '09/08/03  DCK  Original procedure
    
        Dim strChar As String
        Dim intCounter As Integer
        Dim intPosition As Integer
        
        intPosition = 1
        
        For intCounter = 1 To Len(strFilenamePath)
            strChar = Mid(strFilenamePath, intCounter, 1)
            If strChar = "/" Or strChar = "\" Then intPosition = intCounter
        Next intCounter
        
        If intPosition <> 1 Then
            intPosition = intPosition + 1
        End If
        
        'Select the filename, everything after the last slash in the string, also remove the extension
        GetFilenameFromPath = Mid(strFilenamePath, intPosition, 9999)
        
    End Function
    
    Public Function GetPathFromFilename(strFilenamePath As String)
    
    'Description :      This function will return the path of a given
    '                   path and filename
    '
    'Parameters :       strFilenamePath     The path and filename of the file
    '
    'Return :           String.             The path.
    '
    '
    '03/14/06  DCK  Original procedure
    
        Dim strChar As String
        Dim intCounter As Integer
        Dim intPosition As Integer
        
        intPosition = 1
        
        For intCounter = 1 To Len(strFilenamePath)
            strChar = Mid(strFilenamePath, intCounter, 1)
            If strChar = "/" Or strChar = "\" Then intPosition = intCounter
        Next intCounter
        
        If intPosition <> 1 Then
            intPosition = intPosition
        End If
        
        'Select the filename, everything after the last slash in the string, also remove the extension
        GetPathFromFilename = Mid(strFilenamePath, 1, intPosition)
        
    End Function

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    DCK - is your code working for general text - - ?? or whether the field is set as Hyperlink data type ??

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Oh, sorry. I reread and my code works for general text.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the Image field defined as a Hyperlink field? Or is it text? My code sort of works with a Hyperlink. You would just need to tweak it to remove the trailing #.

    Also, I reread your post and you also have to add some code to strip off the extension.

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    I love this forum - Thanks, DCKunkle - I will give this a shot tonight and see how goes.

    The image is not a hyperlink, so I will leave the trailing # intact.

    I will have to strip off the last four characters, I'm assuming I can just use the LEN and RIGHT function...

    If I modify the code at all, I will paste the resulting code in my next reply.

    Thanks a lot, DCKunkle! Will report back

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    Jimmy,

    For you friends art database, the Image File should relate to a unique ID. So if you have ArtworkID = 1 then the Image File should be 1.jpg, that way there is a direct correlation between the work of art and the image, programmatically speaking.

    Simon

  8. #8
    Join Date
    Oct 2009
    Posts
    340
    generically speaking; if the method to link to an image outside of the db (where images belong) is via the hyperlink data type (defined in the table field property) - - then you need to get to know the hyperlink method - - which to 1st time users is very funky because what is visible and what is stored have a wide variance. DCK's generic text manipulation won't work exactly as displayed - but will work as long as it is applied to the appropriate section of the actual hyperlink string..... if one has to mass change alot of hyperlink paths it can be done with a little bit of research.

    If one is starting from scratch it has been recommended to use a basic text field to hold the path string - and then use the FollowHyperlink method to call the image.....this avoids the Hyperlink field type complexity. Haven't had a need to do one of these in a long time - last time I used the hyperlink field type but next time I plan to experiment with this mode.

    The advantage of the hyperlink field type is that what displays can be plain english ; like "My Picture" - and you can hide a long ugly path string....but this is also the hassle when having to do the mass change of that string because it isn't the viewed data....so you do need to research the hyperlink data type in VBA Help and work it thru.

  9. #9
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Hey DCKunkle!

    It worked like a charm. I just added the following to strip off the extension:
    imgLength = Len(GetFilenameFromPath)
    imgTitle = Left(GetFilenameFromPath, (imgLength - 4))
    Me![ImageName] = imgTitle

    That is exactly what I needed!

    I'm kinda proud of myself in that I added it, modified it then tested it - it worked on the first try! I was totally prepared to do battle. It's been months since I've goofed with this stuff.

    SimonMT and NTC, I inherited this database from someone who originally built it in 1997! I agree with both of your approaches, but the truth is, I don't want to get into this any deeper than I have to! Mortgage needs to get paid!!

    Thanks to everyone who offered their help, I know I can always rely on DBForums for the best help around.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •