I need help creating a macro that checks if files exist. I will do my best to explain what is happing and what i need to happen.
column A is just the values in the name that are constant
column B is just and example of the file that gets saved (this column is just a reminder)
column C is what the file names get changed to
Column D is the order they will end up in a pdf
column F&G Are the files that need checked so during check1 im looking for those files(F) and check2 the files in (G)
the last bit of information is the list of files in the folder that i need to look at.
I can only match part of the file names as the rest are not static.
what i need the macro to do: look at the current folder and compare the list of files to the ones in column F. If a file is missing I need it to show the name from column C(corresponding Name) in a listbox of a userform.
For example if looking in folder "XPT" a file called "creditcard_history378494855.pdf" appears it would not display in the list but if it found no file with the partial name "creditcard_" then the list would display "Credit Card History.pdf".
It needs to display these values in a userform listbox NOT in a sheet.
VBA Code:
Sub TestFiles()
Dim myfile As String, counter As Long
Dim f As Long, Cel As Range
Dim fName As String, cName As String
Dim ws As Worksheet, temp As Worksheet, rng As Range
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)
'Loop through all the files in the directory by using Dir$ function
myfile = Dir$("C:\Audit Reports\Disembodied\10-14-2020\*.*")
Do While myfile <> ""
DirectoryListArray(counter) = myfile
myfile = Dir$
counter = counter + 1
Loop
'Reset the size of the array without losing its values by using Redim Preserve
ReDim Preserve DirectoryListArray(counter - 1)
'where is the list
Set ws = Sheets("Data List")
Set rng = ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp))
'insert sheet for results
Set temp = Sheets.Add(before:=Sheets(1))
temp.Range(rng.Address).Value = rng.Value
Set rng = temp.Range(rng.Address)
Dim arr()
Dim s As Integer
ReDim arr(1 To 10000)
t = 1
'check for items in sheet list not in folder
rng.Offset(, 1).Value = "NotFound"
For Each Cel In rng
Do Until f > UBound(DirectoryListArray)
cName = LCase(Cel)
fName = LCase(Left(DirectoryListArray(f), Len(Cel)))
If cName = fName Then
arr(t) = cName
t = t + 1
Exit Do
End If
f = f + 1
Loop
Next Cel
ReDim Preserve arr(1 To t - 1)
ListBox1.List = Application.Transpose(arr)
'listbox1:your listbox name
End Sub
I know some of the code is wrong as i was trying to convert the sheet setup i kept getting from people to the setup i needed.
Any help would be grateful