Auto-select all files in a folder to run a macro

Question

I need a macro to automatically loop through files in a folder and copy certain values from these files. I have the copy part setup on manually selected files, but need to now automatically select them.

I already have a macro setup to loop through multiple files that the user selects. Except now, I'm trying to run it automatically and would like the macro to autoselect any files in a specified directory. How do I specify this directory? I've included my current code for manual selection.

Edit: I tried implementing the below answer, but I recieve an error saying that the file list is not in the format of an array. How do I overcome this?

FileNames = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*;*.csv*", MultiSelect:=True)

ii = 1
Do While ii <= UBound(FileName) 
Set wbk = Workbooks.Open(FileName(ii))

'Multiple Loops
Wend

duplicate of https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba .. or similar at least? — MiguelH, Aug 06 '19 at 13:43
MiguelH, I'm not sure if it's the same thing, that question uses only file properties, while I have to loop through and open each file, copy data, then close it. — hunjah, Aug 06 '19 at 15:47
if your issue is solved May I ask you to accept [this answer](https://stackoverflow.com/a/57378007/11167163) — TourEiffel, Sep 26 '19 at 09:36

score 0 · Accepted Answer · answered Aug 06 '19 at 14:09

Maybe try :

Sub GetAllFileNames()
Dim FolderName As String
Dim FileName As String
FolderName = "C:\Users\sumit\Desktop\Test\" 'change your path
FileName = Dir(FolderName & "*.xls*") 'this one will browse only .xls if you want to browse all file see the note

Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir()
Loop

End Sub

Note : FileName = Dir(FolderName) will browse you all files in folder. If the the workbook is in the same folder than the file you want to browse you can select path like this :

FolderName = Application.ActiveWorkbook.Path & "\"

Auto-select all files in a folder to run a macro

1 Answers1