-10

I am trying to insert pictures inside a xlsm, so I need the code to get the names of the files (jpg files) inside a folder because they all are randomly named

Community
  • 1
  • 1
Alvaro Morales
  • 1,845
  • 3
  • 12
  • 21
  • An answer below uses `Dir` which is good as works across machines. I tend to work in Windows all the time and rather use the `Microsoft Scripting Runtime` library. – S Meaden Apr 30 '18 at 12:44
  • I've seen the code and it works perfectly, would you know how to put the name into an array or something to display them on an excel sheet? – Alvaro Morales May 04 '18 at 11:56
  • here you go http://exceldevelopmentplatform.blogspot.com/2018/05/vba-writing-dictionaries-to-worksheet.html – S Meaden May 04 '18 at 12:13

1 Answers1

2

Dir, called once with your folder and a wildcard (e.g. FileName = Dir("D:\SomeFiles\New Folder\*"): MsgBox FileName) to get the first file in the folder, and repeatedly without any arguments get the rest of the files. (e.g. While Len(FileName)>1: FileName = Dir(): MsgBox FileName: Wend) will run through the files in no particular order.

A more advanced example:

Option Explicit

Private Sub FileDemonstration(FolderName As String)
    Dim FileName As String, FileList() As String, lCount As Long
    lCount = 0
    FileName = Dir(FolderName & "\*")
    While Len(FileName) > 1
        lCount = lCount + 1
        ReDim Preserve FileList(1 To lCount)
        FileList(lCount) = FileName
        FileName = Dir()
    Wend
    'FileList is now an array with all of the files in the folder in it.
    'Do with it what you will
    If lCount < 1 Then
        Debug.Print "No Files Found"
    Else
        For lCount = LBound(FileList) To UBound(FileList)
            Debug.Print "File" & Format(lCount, "000") & ": " & FileList(lCount)
        Next lCount
    End If
End Sub
Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • 2
    Worth mentioning of the file extension in `Dir` if you're looking for a particular file type e.g. `Dir(FolderName & "\*.txt")` for text files. – Kostas K. Apr 30 '18 at 12:50