If Len(Dir(ActualValue)) = 0 Then code excel vba always return true inside for loop

Question

I have an excel vba sub which checks if the file is in the said directory or not. The problem is it always goes to File Saved after the first iteration of the for loop. The value of the ActualValue has some in it if the file is actually found, but if it is missing the value is "". The problem is it always go to the = 0 or = "" Condition.

Dim ActualValue As String

For i = 17 To 32
On Error Resume Next

DateFormat = Format("2017-12-11", "yyyy-mm-dd")

Sheet4.Cells(i, 5).Select
Selection.ClearContents

SearchValue = Sheet4.Cells(i, 1).Value

If SearchValue = "" Then
    MsgBox "No A/C indicated. Please check.", vbOKOnly
    Exit Sub
End If

ActualValue = Dir("A:\123 456\789\abc efg\Sample Folder\SAMPLE FOLDER\" & DateFormat & "\" & SearchValue & "" & "*.xls")

If Len(Dir(ActualValue)) = 0 Then
    Sheet4.Cells(i, 10).Value = "File Saved"
Else
    Sheet4.Cells(i, 10).Value = "File Missing"
    GoTo FileMissing
End If

FileMissing:
Next i

Application.ScreenUpdating = True
End Sub

Why are you putting LEN(DIR? should it not just be Len(ActualValue) — Lowpar, Dec 12 '17 at 08:17
@Joshua -- what is the value of `i`, and thus, what is the value of `Sheet4.Cells(i, 1)`? — ashleedawg, Dec 12 '17 at 08:21
Just put Len(Dir(ActualValue)) into a msgbox and see what you get, or just use the locals window to see what is in it — Lowpar, Dec 12 '17 at 08:24
This is extensively covered here: [VBA Check if file exists](https://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba) — QHarr, Dec 12 '17 at 08:25
@Joshua -- If you're checking if a single file exists, you should only be using the `DIR` function one. Also, I assume your date isn't actually hard coded like that? And that `i` has a value? Some more code (specific to your issue) would be helpful. Check out [mcve] as well as the [tour] and [ask]. Have you searched for existing questions about checking if a file exists? — ashleedawg, Dec 12 '17 at 08:26
@Joshua Aaron please add the rest of your code, your `For i` loop, as the error most likely coming from there. As regards the `Dir` function, as @QHarr mentioned, it's covered in his link — Shai Rado, Dec 12 '17 at 08:30
I have a similar problem to this here: https://stackoverflow.com/questions/47751374/setting-directories-and-the-if-lendir-statement-in-vba — Petrichor, Dec 12 '17 at 09:54

score 0 · Answer 1 · answered Dec 12 '17 at 08:33

DateFormat = Format("2017-12-11", "yyyy-mm-dd") makes no sense.
Format applies a format string to a number or a date. It should therefore read:
DateFormat = Format(#12-11-2017#, "yyyy-mm-dd") if we speak about dec-11.

Also, you could add a Debug.Print ActualValue just after ActualValue calculation, to see if it's returning what you expect. (Be sure to press ctrl+G to display the debug window)

score 0 · Accepted Answer · answered Dec 12 '17 at 09:06

I think is this line DateFormat = Format("2017-12-11", "yyyy-mm-dd") you are trying to convert today's date to a String, in this case use DateFormat = Format(Date, "yyyy-mm-dd").

Also, a safer way to get all Excel file types (including 2003 format) extensions, like .xls, .xlsx and .xlsm is to use .xl??.

Last, in your code there's no need to use On Error Resume Next or use GoTo.

Code

Option Explicit

Sub CheckFilesinDirDate()

Dim ActualValue As String, SearchValue As String, DateFormat As String
Dim i As Long

For i = 17 To 32           
    DateFormat = Format(Date, "yyyy-mm-dd") ' I think you are trying to get today's date int to a String

    Sheet4.Cells(i, 5).ClearContents

    SearchValue = Sheet4.Cells(i, 1).Value        
    If SearchValue = "" Then
        MsgBox "No A/C indicated. Please check.", vbOKOnly
        Exit Sub
    End If

    ActualValue = Dir("A:\123 456\789\abc efg\Sample Folder\SAMPLE FOLDER\" & DateFormat & "\" & SearchValue & "" & "*.xl??")               
    If Dir(ActualValue) <> "" Then
        Sheet4.Cells(i, 10).Value = "File Saved"
    Else
        Sheet4.Cells(i, 10).Value = "File Missing"
    End If
Next i

Application.ScreenUpdating = True

End Sub

Thanks for the tip! but it still didn't work because all iterations went to the else statement for me — Mango Graham, Dec 12 '17 at 09:17
@JoshuaAaron you need to double check the `ActualValue` representing the path and file name match to your foilder (I had a bug when testing it as well) — Shai Rado, Dec 12 '17 at 09:18

If Len(Dir(ActualValue)) = 0 Then code excel vba always return true inside for loop

2 Answers2