Re-code existing VBA

[12Rev79]

Hi Experts,

Currently this code below will give me a list of all PDF only, I want to modify the code;

1. Allow me to select a folder
2. Will list all files in the folder selected could be PDF, .xlsx, .xls, .doc. .docx or Folder name and etc

Private Sub OldName_Click()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder("C:\Users\019\Desktop\DRAWING\COPY DRAWINGS")
i = 1
For Each objFile In objFolder.Files
Cells(i + 2, 2) = objFile.Name
Cells(i + 2, 4) = objFile.Path
i = i + 1
Next objFile

End Sub


Can anyone is kind to help me figure out the code to modify.

Thanks in advance,
12Rev79

 

[offthelip]

try this:

Sub OldName_Click()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(sItem)
i = 1
For Each objFile In objFolder.Files
Cells(i + 2, 2) = objFile.Name
Cells(i + 2, 4) = objFile.Path
i = i + 1
Next objFile

End Sub

 

[12Rev79]

Dear offthelip, thank you I appreciated so much of your help.
But the number 2 step is I think not yet included in the code, sorry if maybe I'm not cleat to that??
What code to add to list also the FOLDER NAME after selecting a folder in directory?
Let say in a selected directory there are mix files .docx, pdf, excel and a Folder.

So I need your help on how to modify the code to include in the list the Folder Names as will

Thank you in advance.

12Rev79

 

[offthelip]

Sorry I missed that detail, try this:

Sub OldName_Click()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim mysub As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(sItem)
i = 1
For Each objFile In objFolder.Files
Cells(i + 2, 2) = objFile.Name
Cells(i + 2, 4) = objFile.Path
i = i + 1
Next objFile

For Each mysub In objFolder.SubFolders
Cells(i + 2, 2) = mysub.Name
Cells(i + 2, 4) = mysub.Path
i = i + 1
Next mysub
 
End Sub

 

[12Rev79]

Thank you offthelip for your kind help appreciated so much, it works will.