Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/questions/50649299/create-an-array-of-file-names-in-a-folder-excel-vba

Sub import_files()
Dim files As String
Dim parentDir As String

parentDir = "folder"
'parentDir = GetFolder() 'UNCOMMENT THIS if you want the user to select a folder via "Windows Explorer"
files = LoopThroughFiles(parentDir, "pdf")
' Debug.Print (files)

Dim iFiles() As String
iFiles() = Split(files, ",")

Dim i As Long
For i = LBound(iFiles) To UBound(iFiles)
    If iFiles(i) <> "" Then
        Debug.Print iFiles(i)
    End If
Next i

End Sub
Private Function LoopThroughFiles(inputDirectoryToScanForFile, filenameCriteria) As String
'https://stackoverflow.com/a/45749626/4650297
Dim tmpOut As String
    Dim StrFile As String
    'Debug.Print "in LoopThroughFiles. inputDirectoryToScanForFile: ", inputDirectoryToScanForFile
    StrFile = Dir(inputDirectoryToScanForFile & "\*" & filenameCriteria)
    Do While Len(StrFile) > 0
        ' Debug.Print StrFile
        tmpOut = tmpOut + "," + StrFile
        StrFile = Dir
    Loop
    LoopThroughFiles = tmpOut
End Function
Function GetFolder() As String
' https://stackoverflow.com/a/26392703/4650297
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function
  • No labels