Results 1 to 3 of 3
  1. #1
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596

    Verify Date Stamp of several files in a folder.

    Hi,



    I want to verify that all the XLSX files in a folder have today's date - and if they do, run a Macro.

    I'm doing something like this . . .:

    Code:
    If DateValue(FileDateTime("C:\Folder\File1.xlsx")) = Date Then
        If DateValue(FileDateTime("C:\Folder\File2.xlsx")) = Date Then
            If DateValue(FileDateTime("C:\Folder\File3.xlsx")) = Date Then
                If DateValue(FileDateTime("C:\Folder\File4.xlsx)) = Date Then
                    If DateValue(FileDateTime("C:\Folder\File5.xlsx")) = Date Then
                        DoCmd.RunMacro "Mcr_MacroName"
                    End If
                End If
            End If
        End If
    End If
    This works fine . . . but . . .
    How would I do this in a loop - [using the Dir function, perhaps]?
    I am having to do this for several different folders in several different parts of the project & the names and numbers of files in these folders can change.
    The constant is that they are all .xlsx files.

    I'd appreciate help! Thanks!!

  2. #2
    John_G is offline VIP
    Windows XP Access 2000
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Yes, the Dir function is what you need to use. The first call to Dir will use a wildcard character to match all the required files. Subequent calls to Dir are used without any arguments; doing that returns the name of the next file that matches the specification; when there are no more matching files, Dir returns an empty string.

    Something like this:

    Dim FileName as string, AllValid as Boolean
    FileName=Dir("C:\Folder\*.xlsx") ' Note use of wildcard character
    AllValid = True
    Do while FileName <> ""
    if DateValue(FileDateTime(FileName)) <> Date then
    '
    ' Exit at first invalid date
    '
    AllValid = False
    exit do
    endif
    '
    ' Get next file name, if any
    '
    Dir
    Loop
    '
    ' Run macro if all dates are valid
    '
    if allvalid then DoCmd.RunMacro "Mcr_MacroName"

    Untested, but it should get you started.

    John

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596

    Thumbs up

    Thanks, John_G.

    This is working well. Appreciate your help.

    I made changes - marked in red.

    I had to include the path to prevent a 'file not found' error.

    I decided not to use the Boolean as I felt it was redundant - since I'm exiting the function at the first sign of a file not matching today's date.
    And - if I have not exited the function, then it means all dates are today so - just run the Macro.

    This is what I ended up using in a function:
    Code:
     
    Dim FilePath, FileName As String, AllValid As Boolean
     
    FileName = Dir("C:\Folder\*.xlsx") 
    FilePath = "C:\Folder\"
     
    Do While FileName <> ""
        If DateValue(FileDateTime(FilePath & FileName)) <> Date Then
           'Exit at first invalid date
            Exit Function
        End If
        'Get next file name.
        FileName = Dir
    Loop
     
    DoCmd.RunMacro "MacroName"
    Once again - thanks for your time & help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date and Time Stamp
    By zoooza84 in forum Access
    Replies: 2
    Last Post: 08-06-2011, 04:53 AM
  2. Replies: 9
    Last Post: 05-30-2011, 12:08 PM
  3. show filters files form a Folder
    By jimmyp75 in forum Programming
    Replies: 5
    Last Post: 03-16-2011, 12:46 PM
  4. Replies: 4
    Last Post: 01-30-2010, 05:22 PM
  5. Date Stamp On Entry of Record
    By tscholts in forum Forms
    Replies: 3
    Last Post: 04-01-2008, 07:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums