aravindhan_3
06-20-2016, 10:51 AM
Hi,
I have this code to get subfolder names and file names from a pre-defined folder path
Sub Extract_File_And_Path()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolder As Object
Dim folpath As String
Dim LastRow As Long
Dim i As Integer
Dim fpath As String
folpath = Range("B10").Value
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.Getfolder(folpath)
i = 14
'loops through each file in the directory and prints their names and path
For Each objSubFolder In objFolder.SubFolders
Range("C3").Select
'print folder name
Cells(i + 1, 3) = objSubFolder.Name
'print folder path
Cells(i + 1, 2) = folpath
'print destination folder path
Cells(i + 1, 4) = Range("D10").Value '& Range("C10").Value
i = i + 1
Next objSubFolder
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
fpath = Range("B10").Value
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.Getfolder(fpath)
i = LastRow
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
Range("C3").Select
'print file name
Cells(i + 1, 3) = objFile.Name
'print file path
Cells(i + 1, 2) = fpath
'print destination folder path
Cells(i + 1, 4) = Range("D10").Value '& Range("C10").Value
i = i + 1
Next objFile
End Sub
I need your help to change this in such a way that instead of taking the path from the cell, it has to ask the user to select/browse the folder & select the subfolder and files which they want ( multiple selection = true) and then those selected file names & subfolder names should be extracted in excel.
Thanks for your help
Arvind
I have this code to get subfolder names and file names from a pre-defined folder path
Sub Extract_File_And_Path()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolder As Object
Dim folpath As String
Dim LastRow As Long
Dim i As Integer
Dim fpath As String
folpath = Range("B10").Value
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.Getfolder(folpath)
i = 14
'loops through each file in the directory and prints their names and path
For Each objSubFolder In objFolder.SubFolders
Range("C3").Select
'print folder name
Cells(i + 1, 3) = objSubFolder.Name
'print folder path
Cells(i + 1, 2) = folpath
'print destination folder path
Cells(i + 1, 4) = Range("D10").Value '& Range("C10").Value
i = i + 1
Next objSubFolder
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
fpath = Range("B10").Value
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.Getfolder(fpath)
i = LastRow
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
Range("C3").Select
'print file name
Cells(i + 1, 3) = objFile.Name
'print file path
Cells(i + 1, 2) = fpath
'print destination folder path
Cells(i + 1, 4) = Range("D10").Value '& Range("C10").Value
i = i + 1
Next objFile
End Sub
I need your help to change this in such a way that instead of taking the path from the cell, it has to ask the user to select/browse the folder & select the subfolder and files which they want ( multiple selection = true) and then those selected file names & subfolder names should be extracted in excel.
Thanks for your help
Arvind