VBA to get File Names & Files Path from a folder to a Excel File

[Morshed_Dhaka]

Hello Everyone,

i am struggling with the below code with 2 specific requirments. Here is my code & requirments :

1. Instead of writing down the folder name here "Set objFolder = objFSO.GetFolder("C:\Users\UserName\FolderName")" , is it possible to use FolderPicker option so that it will visually guide the user to select the folder to collect all the files name & files path details of that selected folder????

2. By using the below code, i am getting file name in a excel file with extension like .xlx / .pdf / .jpg. Is it possible by modifying the below code, i will only get the file name??? i don't want extension name.

Below is my code

Sub FileDetails()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\UserName\FolderName")
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'print file name
Cells(i + 1, 1) = objFile.Name
'print file path
Cells(i + 1, 2) = objFile.Path
i = i + 1
Next objFile
End Sub

will be really gratefull if anyone able to help me for this simple problem. Thanks in advance

 

[Fluff]

How about

Sub MorshedDhaka()
   Dim Fldr As String, Fname As String
   Dim ObjFldr As Object
   Dim i As Long
   
   Set ObjFldr = Application.FileDialog(4)
   With ObjFldr
      .Title = "Choose a folder"
      .AllowMultiSelect = False
      If .Show = -1 Then Fldr = .SelectedItems(1)
   End With
   If Fldr = "" Then Exit Sub
   Fname = Dir(Fldr & "\*")
   Do While Fname <> ""
      i = i + 1
      Cells(i, 1) = Split(Fname, ".")(0)
      Cells(i, 2) = Fldr
      Fname = Dir()
   Loop
End Sub

This assumes that your file names do not have a . in them (other than the file extension)

 

[Morshed_Dhaka]

Hi Fluff, many many thanks. I will try the code and let you know the results. But what if the file name has (.) rather then file extension???

 

[Fluff]

If you do have extra periods I can amend the code, but it's not wise to include a . as part of a filename. Windows can get a bit funny about that sort of thing.

 

[Morshed_Dhaka]

hi fluff, i also not recommend to add (.) as a part of file name but those file names can be saved by employee names which might include (.). so, if you kindly amend the code with that option then it will be helpful

 

[Fluff]

Ok, how about

   Do While Fname <> ""
      i = i + 1
      Cells(i, 1) =[COLOR=#0000ff] Left(Fname, InStrRev(Fname, ".") - 1)[/COLOR]
      Cells(i, 2) = Fldr
      Fname = Dir()
   Loop

 

[Morshed_Dhaka]

Ok, how about

   Do While Fname <> ""
      i = i + 1
      Cells(i, 1) =[COLOR=#0000ff] Left(Fname, InStrRev(Fname, ".") - 1)[/COLOR]
      Cells(i, 2) = Fldr
      Fname = Dir()
   Loop

Hi Fluff,

you are amazing brother. it works perfectly. Although, i believe previous time i didn't described my requirements properly. I find out all my support filled up except 1 thing. I am looking for the full file path name.

Example : C:\Users\UserName\FolderName\test\Morshed_Dhaka Bill Aug 2019.pdf

where Morshed_Dhaka Bill Aug 2019.pdf is the file name & extension. i want to see the full file path name with extension.

this is the last thing i am looking for into that code. Then it will be perfect. again, many thanks for the support.

 

[Fluff]

In that case use

   Do While Fname <> ""
      i = i + 1
      Cells(i, 1) = Left(Fname, InStrRev(Fname, ".") - 1)
      Cells(i, 2) = Fldr & "\" & Fname
      Fname = Dir()
   Loop

 

[Morshed_Dhaka]

Hi Fluff, many thanks. its works like a charm !!!

 

[Fluff]

You're welcome & thanks for the feedback