Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filename Madness (97 SR2)

    I've got over 100 files in a directory, the files use the same format (courtesy of VBA) the format is as follows:

    Usernamemm-dd-yyyy.xls

    I've got code in my workbook which copies the files from one location to another. After it copies the files, it deletes the originals.

    FileCopy FileArray(I), ("ProjectstrackingRepository" & WsRepSheet)
    Kill FileArray(I)

    Where FileArray(I) is the original directory, file name included, and WsRepSheet is the name of the file...

    I would like the code to delete originals only if they are not today's date. Any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    Maybe this can be of any help:
    <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q154821>Get Extended File Time Information Using the Win32 API (Q154821)
    </A>
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    Is there a way to evaluate my filename variable and skip it if a condition exists?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    Drk,

    I have not tested the following so would advise that you test fully with backups. The procedure requires scripting runtime to be registered on your system(s).<pre>Dim fso As Object, fldr As Object, fi As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("ProjectstrackingRepository")
    For Each fi In fldr.Files
    If Not Format(fi.DateLastModified, "mm/dd/yy") = _
    Format(Now(), "mm/dd/yy") Then
    fi.Delete
    End If
    Next
    Set fso = Nothing
    Set fldr = Nothing</pre>

    Andrew

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    In looking at this, it's checking the modified date... unfortunately, this may not jive with when the data was actually created, for various reasons...

    There's no way you can think of to check the filename for the date string?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    Sorry Drk,

    Experiment with <pre>Dim fso As Object, fldr As Object, fi As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("ProjectstrackingRepository")
    Dim intDatePos As Integer
    For Each fi In fldr.Files
    intDatePos = InStr(1, oFile.Name, "-")
    If Not Mid(fi.Name, intDatePos - 2, 10) _
    = Format(Now(), "mm-dd-yyyy") Then
    fi.Delete
    End If
    Next
    Set fso = Nothing
    Set fldr = Nothing
    End Sub</pre>

    The above assumes that the date formated mm-dd-yyyy forms part of the file name and that the minus sign ("-") used between mm and dd is the first occurrence in the filename.

    Or you could possibly use fi.DateCreated in the earlier example.

    Andrew C

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Madness (97 SR2)

    Something like this:

    <pre>Dim strFDate As String
    strFDate = FileArray(I)
    strFDate = Left(strFDate, InStr(strFDate, ".") - 1)
    strFDate = Right(strFDate, Len(strFDate) - InStr(strFDate, "-"))
    If DateValue(strFDate) = Date Then
    MsgBox "Created today."
    Else
    MsgBox "Not today"
    End If
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •