Need Macro to Display subfolder/filename.jpg , not entire objFile.Path and not just objFile.Name

[JRosePhotog]

FIRST TIME POSTING:


I just NEED TO KNOW : how to get the last subfolder and image name to display so that I have a "relative path" instead of a direct path, which is what I'm getting now.

Example of what I want: Player1\img_1234.jpg

What I'm getting: C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player1\IMG_12324.JPG

This is the critical part for my end application that this data will be feeding into. So far I know that I can just change objFile.Path to objFile.Name and I'll get just the file name, but I need that last subfolder to display as well. See my code below.

module 1 code>>
(I have 4 total modules, I'll just make the same change to all)

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player1")
i = 1
'loops through each file in the directory
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 1, 2), Cells(i + 1, 2)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Path
i = i + 1
Next objFile
End Sub

This is the critical part for my end application that this data will be feeding into. So far I know that I can just change objFile.Path to objFile.Name and I'll get just the file name, but I need that last subfolder to display as well.

Thanks for your help!!! I really appreciate it

I've uploaded my .xlsm , however the macro will not generate the file paths for you because you won't have the image files for it to reference unless you create the file structure I reference in each of the 4 Modules:

("C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player1")

("C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player2")

("C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player3")

("C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\Player4")

And this piece of code in each of the 4 Modules I've modified so that in columns 2,4,6,and 8 I will have hyperlinks displayed with the full file path of photos in a list in that column for each Player that has a designated folder with files stored in there. And the Run EM macro will run all 4 at once.

~J Rose Photog


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Marc L]

Just try next demonstration in a new workbook …​

Sub Demo()
    Const P0 = "C:\Users\Jessica\Desktop\J Rose Photog\Sample Files\Event 1\Day 1\Team A\", P1 = "Player1\"
          F$ = Dir$(P0 & P1 & "*.*")
    While F > ""
        R& = R& + 1
        Cells(R, 1).Value = P1 & F
          F = Dir
    Wend
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[JRosePhotog]

It's beautiful! I just need to get the file paths to show up in the correct columns: 2,4,6,8,etc..all even columns, I'll write a module for each column. I'm sure it's just referencing (i,2), (i,4), etc in the code. Going to try it now. Thanks a million!!!!!
~J Rose Photog

 

[JRosePhotog]

Figured it out!

 

[Monty]

Mr JRose.

This should work for you!

Dim donewithparent As Boolean
For Each fsoFol In SourceFolder.SubFolders
    If Not donewithparent Then
        For Each FileItem In fsoFol.ParentFolder.Files
            Cells(i, 1) = FileItem.Name
            Cells(i, 2) = FileItem
            Cells(i, 3) = FileItem.DateLastModified
            i = i + 1
        Next
    End If
    donewithparent = True       
    For Each FileItem In fsoFOL.Files
        Cells(i, 1) = FileItem.Name
        Cells(i, 2) = FileItem
        Cells(i, 3) = FileItem.DateLastModified
        i = i + 1
    Next FileItem
Next fsoFol