Solved

MS Access Parse and Return File Path Name Only

Posted on 2016-10-24
16
19 Views
Last Modified: 2016-10-24
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

0
Comment
Question by:Dustin Stanley
  • 8
  • 4
  • 4
16 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
what is it you want to do with the filename?
I need t for a form so the user can view the product.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:) 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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
That just nailed it! THANK YOU!
0

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

16 Experts available now in Live!

Get 1:1 Help Now