dj44
01-13-2017, 10:32 AM
Hi Folks,
:)
Im trying to get the file names and file paths listed from row
10 in Columns J and K
I've tried to add a few bits in but this simple task is becoming very confusing - and breaking my code.
I am very bad at using resize cells
Public Arr() As String
Public Counter As Long
Sub ListFiles()
'Taken from stack
'http://stackoverflow.com/questions/22392177/find-file-and-insert-path-into-cell
Dim myArr
Dim i As Long
Dim j As Long
Dim MyFile As String
Const strPath As String = "C:\Users\DJ\Desktop\Files\" ' change it as per your needs
myArr = GetSubFolders(strPath)
Application.ScreenUpdating = False
For j = LBound(Arr) To UBound(Arr)
MyFile = Dir(myArr(j) & "\*.txt")
Do While Len(MyFile) <> 0
' How do i get it to start from Row 10?
i = i + 1
Cells(i, 10) = MyFile
Cells(i, 11) = myArr(j)
MyFile = Dir
Loop
Next j
Application.ScreenUpdating = True
End Sub
Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SubFolders
Counter = Counter + 1
ReDim Preserve Arr(Counter)
Arr(Counter) = sf.Path
myArr = GetSubFolders(sf.Path)
Next
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function
Thank you for your help
:)
Im trying to get the file names and file paths listed from row
10 in Columns J and K
I've tried to add a few bits in but this simple task is becoming very confusing - and breaking my code.
I am very bad at using resize cells
Public Arr() As String
Public Counter As Long
Sub ListFiles()
'Taken from stack
'http://stackoverflow.com/questions/22392177/find-file-and-insert-path-into-cell
Dim myArr
Dim i As Long
Dim j As Long
Dim MyFile As String
Const strPath As String = "C:\Users\DJ\Desktop\Files\" ' change it as per your needs
myArr = GetSubFolders(strPath)
Application.ScreenUpdating = False
For j = LBound(Arr) To UBound(Arr)
MyFile = Dir(myArr(j) & "\*.txt")
Do While Len(MyFile) <> 0
' How do i get it to start from Row 10?
i = i + 1
Cells(i, 10) = MyFile
Cells(i, 11) = myArr(j)
MyFile = Dir
Loop
Next j
Application.ScreenUpdating = True
End Sub
Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SubFolders
Counter = Counter + 1
ReDim Preserve Arr(Counter)
Arr(Counter) = sf.Path
myArr = GetSubFolders(sf.Path)
Next
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function
Thank you for your help