Extract file names with extensions and absolute path with VBA code

[IreneFoncillas]

Hi all,

I am trying to create a macro in excel for extract file names (with extension) and their absolute path, from one main path (always a local path), but I am afraid I am not able to.

My idea is to insert in cell C2 the main path, where there are files, folders and subfolders (with files inside them) and get a list of all files with their extension from B4 and absolute path from C4. Something like in the image I attach.

Can anyone help me?

Thank you!!!

 

[GWteB]

This code is likely to do what you're after. Because of the self-repeating behavior of the code (recursion) it was necessary to split the code into two procedures.
The GetFiles procedure actually does the job, although so you have to call the ListFiles procedure for the desired result.

Public Sub ListFiles()

    Dim sPathSource As String, raListFileName As Range, raListPathName As Range

    With ActiveSheet
        sPathSource = .Range("C2").Text
        Set raListFileName = .Range("B5")
        Set raListPathName = .Range("C5")
    End With

    Call GetFiles(sPathSource, raListFileName, raListPathName)
End Sub


Public Sub GetFiles(ByVal argSourcePath As String, ByRef argTopOfFileList As Range, ByRef argTopOfPathList As Range)

    Dim FSO As Object, oRoot As Object, oFile As Object, oFolder As Object
    Dim sPathSource As String

    sPathSource = argSourcePath
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(sPathSource) And Not argTopOfFileList Is Nothing And Not argTopOfPathList Is Nothing Then
        Set oRoot = FSO.GetFolder(sPathSource)
        For Each oFile In oRoot.Files
            argTopOfFileList.Value = oFile.Name
            argTopOfPathList.Value = oFile.ParentFolder.Path
            Set argTopOfFileList = argTopOfFileList.Offset(1)
            Set argTopOfPathList = argTopOfPathList.Offset(1)
        Next oFile
        DoEvents
        For Each oFolder In oRoot.SubFolders
            Call GetFiles(oFolder.Path, argTopOfFileList, argTopOfPathList)
        Next oFolder
    End If
End Sub

 

[jkpieterse]

This is easy enough using Powerquery (In Excel: Data, Get Data, From File, From Folder).

 

[IreneFoncillas]

Thank you both for your response.

VBA Code is super, it works perfectly for what I need.

Regarding Powerquery, I didn't know about that, so thank you for the information. It could work for my purpose but VBA is faster for me. Anyway I will take it into account for further works.

Thanks!!!

 

[GWteB]

You are welcome and thanks for letting us know.