MS Access Parse and Return File Path Name Only

Here is some code that was shared with me to browse and collect a file path. It works Great! I was wanting to return just the file name with this code. I am reading over it and I believe I can alter minimal things in it to just do that but I can't figure it out. My fields currently are ProductImageLocalPath for the full path and ProductImageFileNm for just the file name. Please help me figure out where to alter this code. Thanks.

Option Compare Database
Option Explicit




Function GetFile_Browse( _
   Optional psPathFile As String = "" _
   , Optional psDirectory As String = "" _
   , Optional psTitle As String = "" _
   , Optional pFilters As String = "JPG" _
   , Optional psReturnFilenameOnly As String _
   ) As String
   
'Requires reference to Microsoft Office #.0 Object Library.
's4p 130325...141204
'
   'PARAMETERS
   ' psPathFile -- if sent, will be parsed for the start directory
   ' psDirectory -- if psPathFile not sent, specifies start directory. default is the FE directory.
   ' psTitle = Titlebar of the dialog box
   ' pFilters -- file extension to browse to, ie: JPG, PDF, XLS*
   ' psReturnFilenameOnly - returns the name of the file stripped from the path if sent
   
   On Error GoTo Proc_Err
   
   Dim fDialog As Object 'late binding
'   Dim fDialog As Office.FileDialog 'early binding
   Dim varFile As Variant
   
   Dim sPathFile As String _
      , sStr As String _
      , nPos As Long
      
   If Len(psPathFile) > 0 Then
      'get the directory from psPathFile
      nPos = InStrRev(psPathFile, "\")
      If nPos > 0 Then
         psDirectory = Left(psPathFile, nPos)
      Else
         sPathFile = CurrentProject.Path & "\"
      End If
   Else
      If Len(psDirectory & "") > 0 Then
         'starting directory was specified, psDirectory
         sPathFile = psDirectory
      Else
         'psDirectory not specified, use FE directory
         sPathFile = CurrentProject.Path & "\"
      End If
   End If
         
   With Application.FileDialog(3) 'msoFileDialogFilePicker
      .Filters.Clear
      'use pFilter
      .Filters.Add pFilters & " Files" _
               , "*." & pFilters
      'add All files
       .Filters.Clear
       .Filters.Add "All Files", "*.*"
        .Filters.Add "Jpg", "*.Jpg*"
        .Filters.Add "Bmp", "*.Bmp"
        .Filters.Add "Png", "*.Png"
      
      If Len(psTitle) > 0 Then
         sStr = psTitle
      Else
         sStr = "Choose the Image you would like to import"
      End If
      .Title = sStr
      .InitialFileName = "C:\Users\Shipper Station\OneDrive\Public\ Photos\"
      .AllowMultiSelect = True
      
      If .Show = True Then
         sPathFile = .SelectedItems(1)
      Else
         Exit Function
      End If
   
   End With 'fDialog

   '-------- set return filename
   psReturnFilenameOnly = ""
   nPos = InStrRev(psPathFile, "\")
   If nPos > 0 Then
      psReturnFilenameOnly = Mid(psPathFile, nPos + 1)
   End If
      
   GetFile_Browse = sPathFile
   
Proc_Exit:
   On Error Resume Next
   Set fDialog = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetFile_Browse"

   Resume Proc_Exit
   Resume
End Function

Open in new window

LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
I'm not suggesting you alter the code for GetFile_Browse.

What you could do is return the result of GetFile_Browse to a variable then use that variable to populate the fields.

So something like this.
Public Function AddLocalImagePath() As Variant
Dim strImagePath As String

    strImagePath = GetFile_Browse
    
    If Me.NewRecord Then
        Me!ProductImageLocalPath = strImagePath
        Me!ProductImageFileNm.Value = Dir$(strImagePath)
        DoCmd.GoToControl "sbfrmProductImages"
    Else
        DoCmd.GoToControl "sbfrmProductImages"
        DoCmd.GoToRecord , , acNewRec
        Me!ProductImageLocalPath = strImagePath
        Me!ProductImageFileNm.Value = Dir$(strImagePath)
    End If
    
End Function

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
A "quick'n'dirty" approach would be to replace this line (89 in your code listing above):

GetFile_Browse = sPathFile

With

GetFile_Browse = psReturnFilenameOnly


If you are happy with it then that is fine, but this routine is far from the most elegant solution.
0
 
NorieVBA ExpertCommented:
That function looks as though there was was meant to be an option to only return the filename only but there seems to be bits missing.

It even mentions that option in the comments.
   ' psReturnFilenameOnly - returns the name of the file stripped from the path if sent

Open in new window


If you don't want to alter the existing function you can use something like this to extract the filename.

Dim strProductImageFileNm As String

    strProductImageFileNm = Dir(GetFile_Browse)

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dustin StanleyEntrepreneurAuthor Commented:
GetFile_Browse = psReturnFilenameOnly
That just returned a blank.

seems to be bit's missing
Thats what I thought also.

Dim strProductImageFileNm As String

    strProductImageFileNm = Dir(GetFile_Browse)

Open in new window


How would I place this and use or call it?
0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
GetFile_Browse = psReturnFilenameOnly

That just returned a blank.

In that case (as we are only guessing what is in your variables at that point, & what filename you have selected, given as you have not provided any example data), please see if this is any better...

GetFile_Browse = Dir$(sPathFile)

PS. Line 71 (in your original listing):

.InitialFileName = "C:\Users\Shipper Station\OneDrive\Public\ Photos\"

Is there really a space character before "Photos" in the file path?
0
 
NorieVBA ExpertCommented:
The reason a blank is returned is because it should be sPathFile not psPathFile in that section of the code.

As for using what I posted, when do you want the user to select a file and what is it you want to do with the filename?

Is it to be used in a query?

Put on a form?

Used to open a file?
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I will tell you the big picture here.

I have create a main form with a "AddImagebutton"

Private Sub btnAddImage_Click()
    Forms!frmSKUsEntry!sbfrmProductImages.Form.AddLocalImagePath
End Sub

Open in new window


I have a subform calle "sbfrmProductImages" with code:
Public Function AddLocalImagePath() As Variant
If Me.NewRecord Then
   Me!ProductImageLocalPath = GetFile_Browse
  DoCmd.GoToControl "sbfrmProductImages"
 Else
   DoCmd.GoToControl "sbfrmProductImages"
    DoCmd.GoToRecord , , acNewRec
   Me!ProductImageLocalPath = GetFile_Browse
 End If
End Function

Open in new window


The "GetFile_Browse" Code Returns the complete file path back to the field control "ProductImageLocalPath"


I have just split my database and I am working towards Relative Paths for Linked Images. I am a new learner for the most part but know some.

GetFile_Browse = Dir$(sPathFile)

Open in new window

This does return only the file name but for some reason it also duplicates that name into another field called "ProductImageURL" Also in that same subform and table.
0
 
[ fanpages ]IT Services ConsultantCommented:
I'll let you run with this Norie.  Backing-out as I sense this question may need more time than I have available right now.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
what is it you want to do with the filename?
I need t for a form so the user can view the product.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Backing-out
Your already in way to deep don't do it! Seriously though thank you. I really just kinda get a general idea from my question and then work it into the BIG solution.
0
 
[ fanpages ]IT Services ConsultantCommented:
:) From your reply:

This does return only the file name but for some reason it also duplicates that name into another field called "ProductImageURL" Also in that same subform and table.

I think you probably have an issue with two form fields bound to the same column, or your code (we have not had sight of [yet]) is duplicating the value.

I did not anticipate going beyond your initial (opening) question, as debugging by proxy (without provision of a copy of your database) may prove difficult.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Is there really a space character before "Photos" in the file path?

Sorry no there really isn't and the code is correct also. I messed up when inserting the code here.
It really is .InitialFileName = "C:\Users\Shipper Station\OneDrive\Public\Photos\"
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I did not anticipate going beyond your initial (opening) question, as debugging by proxy (without provision of a copy of your database) may prove difficult.

Its all good This will give me something to go off of. Thanks!
0
 
NorieVBA ExpertCommented:
I wouldn't suggest changing the code of the function GetFile_Browse.

Which field would you want just the filename returned to?

If it was a field called ProductImageFileNm you could use this.
Me!ProductImageFileNm = Dir$(GetFile_Browse)

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok in my GetFile_Browse if I change it to
GetFile_Browse = Dir$(sPathFile)

Open in new window


And in my subform change it to:
Public Function AddLocalImagePath() As Variant
If Me.NewRecord Then
   Me!ProductImageFileNm = GetFile_Browse
  DoCmd.GoToControl "sbfrmProductImages"
 Else
   DoCmd.GoToControl "sbfrmProductImages"
    DoCmd.GoToRecord , , acNewRec
   Me!ProductImageFileNm = GetFile_Browse
 End If
End Function

Open in new window


It works perfectly.

I wouldn't suggest changing the code of the function GetFile_Browse.

BUT I like the idea of not altering the code if that could be done?

Me!ProductImageFileNm.Value = Dir$(GetFile_Browse)

That is the correct field I would need. So if  I use the GetFile_Browse and insert the full path into the field called "ProductImageLocalPath" then how and where would I place the code "Me!ProductImageFileNm.Value = Dir$(GetFile_Browse)" To parse from the full path field?

Thank you All for the help!!
0
 
Dustin StanleyEntrepreneurAuthor Commented:
That just nailed it! THANK YOU!
0
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.