Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Calgary, Alberta, Canada
    Thanked 1 Time in 1 Post

    Question Not sure how to do this - missing file numbers in a sequence of file names

    I have a number of file folders that store camera images. For the most part, in anyone folder, these file numbers are sequential, e.g., IMG2001.cr2, IMG2002.cr2, IMG2003.cr2, etc. Every so often, one of the numbers is missing and I would like to be able to get a list of the file names, import it into Excel (somehow) and then use Excel to find the missing numbers. If there is another relatively easy way to do this I would also be interested in knowing about it. If not, then I think that there must be some way to do this in Excel by looking at each file name and determining if the difference is greater than one from the previous name and then flagging these two file names when this happens, maybe using conditional formatting. Right now, the file names are mixed alphanumeric with a three character file type on the end, e.g., cr2, jpg, tif, so it might be necessary to separate the two parts of the file name, alpha part and numeric part, as well as the file ending, and I am not quite sure how to do that. Any insight into how to do this will also be appreciated.

    If I only had a few hundred files to contend with, this would not be a problem, but I have a few thousand to contend with and doing it manually is not really feasible. I am hoping that someone has run into this problem before, or one that is similar to it and has found a solution that they are willing to share. Any insight into how to do this is greatly appreciated. Thanks.

    Ron M
    Last edited by Ron M; 2012-12-13 at 15:26.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,473 Times in 1,340 Posts

    Here's some code to get you started.
    Option Explicit
    '                    +------------------------------+             +----------+
    '--------------------|       Locate_Files()         |-------------| 12/13/12 |
    '                    +------------------------------+             +----------+
    'Parameters: zPath - A string with the Directory\Path information to search.
    'Returns:    True:  Files found
    '            False: No matching files found.
    Sub Locate_Files()
       Dim iFNLen    As Integer
       Dim lListCntr As Long
       Dim zDir      As String
       Dim zFilePath As String
       Dim zFileName As String
       Dim vSplitIt  As Variant
       lListCntr = 0
       zDir = [B1].Value
       zFilePath = Dir(zDir & "\*.jpg") 'Change to your File Extension
       Do While zFilePath <> ""
        '*** Compute File Name ***
        vSplitIt = Split(zFilePath, "\")
        zFileName = vSplitIt(UBound(vSplitIt))
        iFNLen = Len(zFileName) - 4
        zFileName = Left(zFileName, iFNLen)
        '*** End Compute File Name ***
        lListCntr = lListCntr + 1
        '*** Assign Filename to Next Cell ***
        With ActiveCell
            .Offset(lListCntr, 0).Value = zFileName
    '*** Formula to compute out of sequence numbers ***
    '=IF(VALUE(RIGHT(RC[-1],4))=VALUE(RIGHT(R[-1]C[-1],4))+1,"","Out of Sequence")
            .Offset(lListCntr, 1).FormulaR1C1 = _
                   "=IF(VALUE(RIGHT(RC1,4))=VALUE(RIGHT(R[-1]C1,4))+1," & Chr(34) & Chr(34) & _
                   "," & Chr(34) & "Out of Sequence" & Chr(34) & ")"
        End With
        zFilePath = Dir() 'Look for Next file
       Loop    'Loop while there are files in the directory
       [B3].ClearContents   '*** Get rid of formula since 1st one can't be out of sequence ***
    End Sub        'Locate_Files()
    Note these limitations.
    1. Only one directory at a time.
    2. Assumes all files have a 4 digit number.
    3. Assumes that the files in the directory were loaded in numerical order.
    4. Assumes the file extension is 4 characters in length including the period.
    5. A1, A2 are setup as shown.
    6. B1 contains the complete directory path {note the one shown in the example is a NAS drive thus no drive letter}.

    All of these limitations can be overcome with more code.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Calgary, Alberta, Canada
    Thanked 1 Time in 1 Post
    Thanks RG. This sort of stuff is a little beyond my comfort zone, but I will give it a try in the next few days. At least I have written code many moons ago, so the idea of using code is not foreign, just soooo out of date -

    Ron M

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Thanked 1,017 Times in 946 Posts
    Like the ASCII art!

    cheers, Paul

Posting Permissions

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