TOP VBA

EXCEL-VBA FOR QC ENGINEERS

GET FILE NAME IN EXCEL BY VBA

If you have several files in a folder and you want
 to write each file name in excel worksheet 

then below is the code.  

 --------------------------------------------

Private Sub Workbook_Open()

 Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

Dim FLD As Office.FileDialog
Set FLD = Application.FileDialog(msoFileDialogFolderPicker)

FLD.Show

Dim P As String
P = FLD.SelectedItems(1)

Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject

Dim FOL As Scripting.Folder
Set FOL = FSO.GetFolder(P)

Dim F As Scripting.File
Dim r As Long

r = 2

For Each F In FOL.Files

    Cells(r, 1).Value = F.Name
    r = r + 1
 
Next F

End Sub

few screenshots are for reference-

filename

as you open it will prompt to select a folder where files are kept

select the folder, and see backside it is empty

immediately all the files name are copied in excel sheet.. enjoy.

"BOM" IS MY FOLDER FOR EXAMPLE

THESE ARE MY FILES WHICH NAME I HAVE TO WRITE IN EXCEL SHEET

                              see video

IF ANY DOUBT, PLEASE WRITE TO ME.