Dir() only works sometimes

[liz_sully]

Hello,

I have a function that finds the corresponding file path and partial file name for my input, and then uses Dir() to generate the full, actual file name. Some of my file paths are C://myfilepath/YYYY/file, and some are C://myfilepath/YYYY/MM/file. I am using <YYYY> and <MM> labels, then replacing them with the specific dates needed.

Dir() is not working properly for files that have MM in their file paths (it works fine for files that only have YYYY). Does anyone have any ideas as to why this is, or what I can do to fix it? I have added watches to all my variables and they are populating fine up until the Dir() step.

Thanks in advance!

Function GetFileName() As String

    Dim strReportName As String
    Dim intEndFileList As Integer
    Dim n As Integer
    Dim strFilePath As String
    Dim strFileStart As String
    Dim strFileName As String
    
    strReportName = wsForm.Range("F4").Value


    intEndFileList = wsFilePath.UsedRange.Rows.Count
    
        'For the chosen report, find the folder path and beginning of file name
        For n = 2 To intEndFileList
        If wsFilePath.Cells(n, 1).Value = strReportName Then
            strFilePath = wsFilePath.Cells(n, 2).Value
            strFileStart = wsFilePath.Cells(n, 3).Value
            
        End If
        
        Next n
        
        strFilePath = Replace(strFilePath, "<YYYY>", Year(wsForm.Calendar1.Value))
        strFilePath = Replace(strFilePath, "<MM>", Format(Month(wsForm.Calendar1.Value), "00"))
        strFileStart = Replace(strFileStart, "<YYYY>", Year(wsForm.Calendar1.Value))
        strFileStart = Replace(strFileStart, "<MM>", Format(Month(wsForm.Calendar1.Value), "00"))
            
    strFileName = Dir(strFilePath & strFileStart & "*.csv")
               
    GetFileName = strFilePath & strFileName
     
End Function

 

[JLGWhiz]

Hello,

I have a function that finds the corresponding file path and partial file name for my input, and then uses Dir() to generate the full, actual file name. Some of my file paths are C://myfilepath/YYYY/file, and some are C://myfilepath/YYYY/MM/file. I am using <yyyy> and <mm> labels, then replacing them with the specific dates needed.

Dir() is not working properly for files that have MM in their file paths (it works fine for files that only have YYYY). Does anyone have any ideas as to why this is, or what I can do to fix it? I have added watches to all my variables and they are populating fine up until the Dir() step.

Thanks in advance!

Function GetFileName() As String

    Dim strReportName As String
    Dim intEndFileList As Integer
    Dim n As Integer
    Dim strFilePath As String
    Dim strFileStart As String
    Dim strFileName As String
    
    strReportName = wsForm.Range("F4").Value


    intEndFileList = wsFilePath.UsedRange.Rows.Count
    
        'For the chosen report, find the folder path and beginning of file name
        For n = 2 To intEndFileList
        If wsFilePath.Cells(n, 1).Value = strReportName Then
            strFilePath = wsFilePath.Cells(n, 2).Value
            strFileStart = wsFilePath.Cells(n, 3).Value
            
        End If
        
        Next n
        
        strFilePath = Replace(strFilePath, "<yyyy>", Year(wsForm.Calendar1.Value))
        strFilePath = Replace(strFilePath, "<mm>", Format(Month(wsForm.Calendar1.Value), "00"))
        strFileStart = Replace(strFileStart, "<yyyy>", Year(wsForm.Calendar1.Value))
        strFileStart = Replace(strFileStart, "<mm>", Format(Month(wsForm.Calendar1.Value), "00"))
            
    strFileName = Dir(strFilePath & strFileStart & "*.csv")
               
    GetFileName = strFilePath & strFileName
     
End Function

Not positive this will work, but it won't hurt to try. Change this statement

Format(Month(wsForm.Calendar1.Value), "00"))

To this

Format(wsForm.Calendar1.Value, "mm")

I think the problem is a data type problem in the way Excel sees the month value.
</mm></yyyy></mm></yyyy></mm></yyyy>

 

[liz_sully]

Thanks for your input. I tried your suggestion, but still having the same issue.

On the other hand, I do think this code looks cleaner, so thank you for that!

 

[liz_sully]

Update: I think I have an idea as to why it isn't working. For the files it's having issues with, there are multiple files in that folder with the same starting string (and different versioning numbers at the end).

Does anyone have any ideas on how to choose the latest file, maybe based on timestamps? The issue is that the full file name includes the time the file was created (ie, File_YYYYMM_hh_mm) and I cannot predict what the exact hh and mm values will be.

 

[JLGWhiz]

Update: I think I have an idea as to why it isn't working. For the files it's having issues with, there are multiple files in that folder with the same starting string (and different versioning numbers at the end).

Does anyone have any ideas on how to choose the latest file, maybe based on timestamps? The issue is that the full file name includes the time the file was created (ie, File_YYYYMM_hh_mm) and I cannot predict what the exact hh and mm values will be.

You might be better off just using 'Application.GetOpenFilename' and choosing from the dialog box.