?
Solved

MS Access Parse and Return File Path Name Only

Posted on 2016-10-24
16
Medium Priority
?
47 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 4
16 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41857601
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 34

Expert Comment

by:Norie
ID: 41857606
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
ID: 41857612
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 1000 total points
ID: 41857624
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 34

Expert Comment

by:Norie
ID: 41857625
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
ID: 41857634
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 ]
ID: 41857636
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
ID: 41857637
what is it you want to do with the filename?
I need t for a form so the user can view the product.
0
 

Author Comment

by:Dustin Stanley
ID: 41857639
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 ]
ID: 41857646
:) 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
ID: 41857650
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
ID: 41857652
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 34

Expert Comment

by:Norie
ID: 41857656
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
ID: 41857681
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 34

Accepted Solution

by:
Norie earned 1000 total points
ID: 41857692
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
ID: 41857698
That just nailed it! THANK YOU!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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