Hey,
I'm writing a macro to fix my music collection. The first part is just choosing the folder to search and the file to find. So it loops to find all sub folders to check (by the FileList function), in looping through these folders. In each folder it gets the FileArray (by FileList function) to start checking for the 'FindFile'. But here it stops at "ERROR LINE". I had working in parts, but together its failing with Error13 Mismatch and I don't know why.
Sub FindFiles()
Dim FolderArray() As String, FileArray() As String, SubDirectories() As String, FileList As Variant
Dim StartFolder As String, FindFile As String, MissingFile As String
Dim SubNumber As Integer, Start As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim checkingNow As String
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
Length = WorksheetFunction.CountA(ws1.Range("G:G"))
For RowN = 2 To Length
MissingFile = ws1.Range("G" & RowN)
SubDirectories = Split(MissingFile, "\")
SubNumber = UBound(Split(MissingFile, "\"))
Start = Len(MissingFile) - Len(SubDirectories(SubNumber - 2) & SubDirectories(SubNumber - 1) & SubDirectories(SubNumber))
StartFolder = Left(MissingFile, InStr(Start, MissingFile, "\"))
'MsgBox (StartFolder)
FindFilde = SubDirectories(SubNumber)
FolderArray() = GetSubFolders(StartFolder)
If TypeName(FolderArray) <> "Boolean" Then
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LOOP FOLDERS %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
For p = LBound(FolderArray) To UBound(FolderArray)
'ERROR LINE
FileArray() = FileList(FolderArray(p))
If TypeName(FileArray) <> "Boolean" Then
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LOOP FILES %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
For i = LBound(FileArray) To UBound(FileArray)
'MsgBox FileArray(i)
'%%%%%%%%%%%%%%%%%%%%%%%%%%%% FOUND FILE! %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
If FindFile = FileArray(i) Then
ws1.Range("I" & RowN) = FolderArray(p)
ws1.Range("J" & RowN) = FileArray(i)
GoTo NextSong
End If
Next i
Else
MsgBox "No files found"
End If
Next p
Else
MsgBox "No folders found"
End If
NextSong:
Next RowN
End Sub
and the functions:
Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
Dim sTemp As String, sHldr As String
If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
sTemp = Dir(fldr & fltr)
If sTemp = "" Then
sTemp2 = Dir(fldr)
FileList = False
Exit Function
End If
Do
sHldr = Dir
If sHldr = "" Then Exit Do
sTemp = sTemp & "|" & sHldr
Loop
FileList = Split(sTemp, "|")
End Function
Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr(), Arr() As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SUBFOLDERS
ReDim Preserve Arr(Counter)
Arr(Counter) = sf.Path
Counter = Counter + 1
'myArr = GetSubFolders(sf.Path)
Next
ReDim Preserve Arr(Counter)
Arr(Counter) = RootPath
n = UBound(Arr)
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function
Thanks peeps cause I'm lost!!!
Bookmarks