Get file name from folder in excel file

[atuljadhavnetafim]

Hi,

i have 1000+ PDF files in one folder, now i want only file name in excel from A2.
any macro for that else i need to copy file name (F2) and paste in excel.

file store at C:\2022-23\Q2\Certificates

 

[HaHoBe]

Hi atuljadhavnetafim,

maybe

Public Sub MrE_1225548_1616A0B()
' https://www.mrexcel.com/board/threads/get-file-name-from-folder-in-excel-file.1225548/
' Created: 20221228
' By:      HaHoBe

Dim strFile       As String
Dim lngWrite      As Long

Const cstrPath As String = "C:\2022-23\Q2\Certificates"

strFile = Dir(cstrPath & "*.pdf")
ActiveSheet.UsedRange.ClearContents

lngWrite = 1
Do While strFile <> ""
  lngWrite = lngWrite + 1
  ActiveSheet.Cells(lngWrite, "A").Value = strFile
  strFile = Dir
Loop
End Sub

Ciao,
Holger

 

[HongRu]

How about this ?
HTH

Sub sbFileName()
    Dim myPath
    myPath = "C:\2022-23\Q2\Certificates\"
    
    Dim myFilename
    myFilename = Dir(myPath)
    
    Range("A1") = "FileName" 'If you already have a title, delete the line.
    Do Until myFilename = ""
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = myFilename
        myFilename = Dir
    Loop
End Sub

 

[atuljadhavnetafim]

Hi,

it's not working, after run macro nothing coming in excel file nor show any error.
can you please check again

i need excel copy file name and paste A2 then go to second file and paste A3 so on till last file

 

[jdellasala]

Hmmm... In Windows, click Start, type "Command" and select "Command Prompt". You should end up in a terminal window with the prompt C:\Users\USERID> (where USERID is your computer login ID).
type "CD Desktop" and hit [Enter]. The prompt will change to C:\Users\USERID\Desktop>.
Type "DIR C:\2022-23\Q2\Certificates\*.PDF /B >PDFList.txt" and hit [Enter]. If there are subdirectories (folders) under the Certificates directory, add " /S" after /B in that command.
This will create the text file C:\Users\USERID\Desktop\PDFList.txt. It will be a list of all the files in the Certificates folder and sub folders if you used /S with all the file names with the full path such as C:\2022-23\Q2\Certificates\PDFFile.pdf.
You can now open the text file in Excel which will run the Text/CSV Import wizard, or use Power Query where it would be a lot easier to strip the path from the filename.

 

[atuljadhavnetafim]

Yes, its' working as required,
thanks for your prompt support.

 

[HongRu]

Yes, its' working as required,
thanks for your prompt support.

Glad to help.