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"
File checker array
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
File checker array
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: File checker array
Are you looking for files with a specific date (e.g. 2009-11-04) or for any date?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: File checker array
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"
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: File checker array
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)
strFile = Left(strFile, Len(strFile) - 9)
(This assumes that strFile doesn't include the file extension)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: File checker array
Sorry Hans, I'm confused. How would I use that with:
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.
?
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
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.
?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: File checker array
Oops, sorry.
Code: Select all
Function FileExists(strFullName As Variant) As Boolean
FileExists = Not (Dir(strFullName) = "")
End Function
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: File checker array
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: File checker array
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?
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?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: File checker array
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"
FileCopy strPathB & Dir(strPathB & varFile & "*.xls"), strPath3 & Left(varFile, Len(varFile) - 10) & Format(strDate, "dd.mmm.yy") & ".xls"
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.