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.
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