How to extract file name with extension from parent path? MS Access VBA

Question

I want to get the name of the file with its extension after the parent file path. For example: retrieve picture.jpg from C:\Desktop\Pictures\picture.jpg. *Note: I do not know what the name of the file or picture is going to be. Users will add those themselves and I want to retrieve the name to display it on a form.

Here is what I have so far...

Private Sub InsertImageButton_Click()
Dim strImageID As String
Dim strFolder As String
Dim fso As Object
Dim strFormat As String

Const strParent = "C:\Desktop\Pictures\"

strImageID = Me.ImageID.Value
strFormat = Format(strImageID, "20-0000")

'Parent Path
strFolder = strParent & strFormat

'Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'Check Whether Folder Exists
If fso.FolderExists(strFolder) = False Then
'If not, create it
    fso.CreateFolder strFolder
End If

'Open It
Shell "C:\Windows\explorer.exe """ & strFolder & "", vbNormalFocus

*When the folder opens, this is where the user should insert a new picture. After this I want the textbox to display the full path name so it can get the name of the picture as well.

'This is where I am stuck.
Me.[txtImageName].Value = strFolder & "\" & GetFilenameFromPath(strFolder)

I tried adding a function from other Stack Overflow questions but it didn't help.

Public Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
Dim picturepath As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

The current code gives me C:\Desktop\Pictures\20-0012\20-0012 I need the name of the picture added without knowing what was added. Thanks in advance!

I tested GetFilenameFromPath function and it returns "picture.jpg" from "C:\Desktop\Pictures\picture.jpg". Code is unnecessarily complex but it works. So whatever issue is, it's not with that function. — June7
– June7, Commented Aug 28, 2020 at 18:11
Me.[txtImageName].Value = strFolder & "\" & GetFilenameFromPath(strFolder). So is this correct? I am not sure how to use the function and txtImageName.Value at the same time? — SharonIsKaren
– SharonIsKaren, Commented Aug 28, 2020 at 18:16
The function returns correct output when it receives a full filepath string. Probably need to modify FSO dialog code to select file, not folder. Why are you opening Windows Explorer? — June7
– June7, Commented Aug 28, 2020 at 18:24
The problem is that I don't know the full filepath string. I only know the parent filepath. I need to get picture.jpg by only being able to see C:\Desktop\Pictures\ (picture.jpg is the unknown and I need a way to find it). — SharonIsKaren
– SharonIsKaren, Commented Aug 28, 2020 at 18:33
If it is a single file inside, no need to loop. Try my suggestion in the adapted code, please. — FaneDuru
– FaneDuru, Commented Aug 28, 2020 at 18:46

FaneDuru · Accepted Answer · 2020-08-28 18:45:46Z

2

Try the next function, please:

Function GetFileName(strFullName As String) As String
    GetFileName = Split(strFullName, "\")(UBound(Split(strFullName, "\")))
End Function

The above function should be tested in this way, if the folder in discussion contains a single file:

dim fullFileName As String
fullFileName = Dir(strFolder & "\*.jpg")
MsgBox GetFileName(fullFileName)

edited Aug 28, 2020 at 18:45

answered Aug 28, 2020 at 18:18

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

SharonIsKaren Over a year ago

Should I replace strFullName with my strFolder?

FaneDuru Over a year ago

@SharonIsKaren: Yes. I edited the answer in order to show how to be tested/called.

SharonIsKaren Over a year ago

MsgBox still give me 20-0020. strFolder is only C:\Desktop\Pictures\20-0020 I need the file inside it, which should be another file that's unknown.

FaneDuru Over a year ago

I asked you in a comment: What Me.ImageID.Value returns? Isn't it the file name?

FaneDuru Over a year ago

Is it a jpg file?

|

AVG · Accepted Answer · 2020-08-28 18:13:23Z

0

Public Function GetFilenameFromPath(ByVal strPath As String) As String
    GetFilenameFromPath = Mid(strPath, InStrRev(strPath, "\") + 1)
End Function

answered Aug 28, 2020 at 18:13

AVG

1,4621 gold badge9 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to extract file name with extension from parent path? MS Access VBA

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related