run a check on all files IN A FOLDER

[oldmanwilly]

Hi

I need help i haven't been able to work out the best way to do this:

refresh a formula in myworkbook >>> check to see if a list of file names in my worksheet match the files that are in the same folder >>>if they are, then put a yes next to the file name cell and also put the number of rows that the file has in the cell next to that one>>>if the name isn't there then just leave blank.

I have come up with the code but some bits i cant get to work, please note cell i2 contains the formula =left(cells("filename"),83), this is the only way i could think of to get the workbooks folder path.

Thanks for any help

Sub CHECKFILES()

' IN THIS FOLDER CHECK WHETHER THE FILENAME MATCHES THE ONE IN COLUMN A
Dim MYFOLDER As String
Dim myval As String
Dim myrow As Long
Dim mybook As Workbook

mybook = ThisWorkbook


MYFOLDER = Range("i2").Text

'check if the name matches the cell

For i = 2 To 67
myval = Range("a" & i).Text
if Seek.myval in myfolder = true then
myrow = 'in myfolder & myval count number of rows -1
 mybook.Activate
 
 Range("c" & i).Value = 1
 Range("d" & i).Value = myrow
 
 Else
 Range("c" & i).Value = 0
 End If
 Next i

 

[ranman256]

adjust as needed:

' IN THIS FOLDER CHECK WHETHER THE FILENAME MATCHES THE ONE IN COLUMN A
Sub CHECKFILES()
Dim MYFOLDER As String
Dim myval As String
Dim myrow As Long
Dim mybook As Workbook
Dim vFile
Dim i As Integer


Set mybook = ThisWorkbook


MYFOLDER = Range("i2").Text


'check if the name matches the cell


For i = 2 To 67
    myval = Range("a" & i).Text
    vFile = MYFOLDER & myval
    
    If FileExists(vFile) Then
       Range("c" & i).Value = 1
       Range("d" & i).Value = myrow
     Else
        Range("c" & i).Value = 0
    End If
 Next i
End Sub


Public Function FileExists(ByVal pvFile) As Boolean
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FileExists = FSO.FileExists(pvFile)
Set FSO = Nothing
End Function

 

[oldmanwilly]

adjust as needed:

' IN THIS FOLDER CHECK WHETHER THE FILENAME MATCHES THE ONE IN COLUMN A
Sub CHECKFILES()
Dim MYFOLDER As String
Dim myval As String
Dim myrow As Long
Dim mybook As Workbook
Dim vFile
Dim i As Integer


Set mybook = ThisWorkbook


MYFOLDER = Range("i2").Text


'check if the name matches the cell


For i = 2 To 67
    myval = Range("a" & i).Text
    vFile = MYFOLDER & myval
    
    If FileExists(vFile) Then
       Range("c" & i).Value = 1
       Range("d" & i).Value = myrow
     Else
        Range("c" & i).Value = 0
    End If
 Next i
End Sub


Public Function FileExists(ByVal pvFile) As Boolean
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FileExists = FSO.FileExists(pvFile)
Set FSO = Nothing
End Function

hey that works for checking if the file exists thanks! just two things though.

1. it doesn't put a value in the myrow variable do you know how to do that step?
2. is there a way for me to not stipulate the extension in the lookup, e.g. i put man.xls it looks for an xls file, put sometimes files are saved as xlsx so i want to also look for these files.

3. what is the function doing? i;ve never seen that before

thansk !

 

[oldmanwilly]

hi did you understand my reply?