File checker array

[VegasNath]

I have created some vba code to check the existance of an array of dated files for their existance which are formatted as follows:

Variable length file name - yyyy-mm-dd-hh-mm-ss

How can I either ignore the "-hh-mm-ss" or add wildcards to the search?

My check needs to be for "Variable length file name - yyyy-mm-dd"

[HansV]

Are you looking for files with a specific date (e.g. 2009-11-04) or for any date?

[VegasNath]

I need to check that certain files exist with a specific date. Once I have checked this, I need to copy them from the source location to another destination with the same file name, less the "-hh-mm-ss"

[HansV]

The length of the "-hh-mm-ss" part is 9 characters, so you could chop off the last 9 characters of the name:

strFile = Left(strFile, Len(strFile) - 9)

(This assumes that strFile doesn't include the file extension)

[VegasNath]

Sorry Hans, I'm confused. How would I use that with:

Code: Select all

    arrFiles1 = Array( _
        "Report 5.8- - " & Format(strDate1, "yyyy-mm-dd") & ".xls", _
        "Report 5.16-5.19 - " & Format(strDate1, "yyyy-mm-dd") & ".xls", _
        "Report 5.20 - " & Format(strDate1, "yyyy-mm-dd") & ".xls", _
        "Report 5.30- - " & Format(strDate1, "yyyy-mm-dd") & ".xls", _
        "Payments - " & Format(strDate1, "yyyy-mm-dd") & ".xls")
           
    For Each varFile In arrFiles1
        If FileExists(strPathB & varFile) = False Then
            varInfo = varInfo & strPathB & varFile & vbCrLf & vbCrLf
        End If

I (guess) I can use:

FileCopy strPathB & varFile, strPath3 & Left(varFile, Len(varFile) - 23) & Format(strDate, "dd.mmm.yy") & Right(varFile, 4)

To copy the file, less the date, time, and extention, add on a new reformatted date and the extention.

?

[HansV]

What is FileExists?

[VegasNath]

Oops, sorry.

Code: Select all

Function FileExists(strFullName As Variant) As Boolean
    FileExists = Not (Dir(strFullName) = "")
End Function

[HansV]

I'd use

Code: Select all

    arrFiles1 = Array( _
        "Report 5.8- - " & Format(strDate1, "yyyy-mm-dd"), _
        "Report 5.16-5.19 - " & Format(strDate1, "yyyy-mm-dd"), _
        "Report 5.20 - " & Format(strDate1, "yyyy-mm-dd"), _
        "Report 5.30- - " & Format(strDate1, "yyyy-mm-dd"), _
        "Payments - " & Format(strDate1, "yyyy-mm-dd"))

    For Each varFile In arrFiles1
        If FileExists(strPathB & varFile & "*.xls") = False Then
            varInfo = varInfo & strPathB & varFile & ".xls" & vbCrLf & vbCrLf
        End If

[VegasNath]

Thanks Hans, I'm nearly there, honest!

That works, but I am getting a rte52 "bad file name" on the copy / rename

FileCopy strPathB & varFile & "*.xls", strPath3 & Left(varFile, Len(varFile) - 10) & Format(strDate, "dd.mmm.yy") & ".xls"

It appears to not like the copy with the wildcard?

[HansV]

The following line should copy the first file that matches the wildcard.

FileCopy strPathB & Dir(strPathB & varFile & "*.xls"), strPath3 & Left(varFile, Len(varFile) - 10) & Format(strDate, "dd.mmm.yy") & ".xls"

[VegasNath]

Excellent, Thanks Hans