Comparing files in two different folders with string variable names

Question

Due to automatic naming conventions I'm stuck with, files are saved in a particular archive one of two ways. Either they have a 13-digit match to the record in the database, or they have a 10 digit match. The ones which have the 13 digit also have the ten digit match, and should be treated preferentially. I have two scripts right now that work: one just looks for 13-digit matches and moves them to another folder. The other moves by datemodified, which is not a very stable variable.

My attempts at combining look something like this:

'dimming of obj, folder paths, sql db info and connection open above
SQL = "SELECT ticket FROM orders WHERE dateinvoice>'6/1/16"
Recordset.Open SQL, Connection

Do While Not Recordset.EOF
  ticket  = Recordset("ticket")
  id      = Right(ticket, 2)
  suffix  = CInt(id)
  compare = Left(ticket, Len(ticket) - 3)

  search  = compare & "-0" & suffix

  For Each objFile In colFiles.Files
    If Left(objFile.Name, 13) = search Then
    Set objNewestFile = objFile
      skip = 1
    Else
      skip = 0
    End If

    If Left(objFile.Name, 10) = search And skip = 0 Then
      Set objNewestFile = objFile
    End If
  Next

  Recordset.MoveNext

  On Error Resume Next
  objFSO.CopyFile objNewestFile.Path, strDestFolder

  Set objFile = Nothing
Loop

This is the scaled-down version I went back to, having attempted various convoluted solutions like:

storing the skipped records in an array,
having a second loop through comparing files in folder1 to folder2 (every time I use FileExists it doesn't seem to recognize the destination),
creating a temporary SQL table to store the skipped records in.

The following have been very useful:

ETA: There can be many duplicate files that share the 10 digit identifier. The only good rule is: if it has the 13 identifier version, that's the one to move. Part of my issue lies in setting paths if I use FileExists-- when I try wildcards, it doesn't recognize the path. If I don't use them, it doesn't compensate for all the variables.

You say you have files in two folders, but you're checking just one. What are the folders, and what does their content look like? Can there be duplicate files per {10|13}-digit ID? What do you want to copy/move from where to where *exactly*? — Ansgar Wiechers, Aug 23 '16 at 08:05
@Ansgar Wiechers- My (obviously faulty) logic in the single loop version was to catch the 13 digit files and, if one did not exist, still net the 10-digit one. That's probably not possible, now that I've thought about it with more sleep. The origin folder is the archive, which can have many duplicates of both 10 or 13 digit designations (these are followed by a timestamp). The folder I'm moving to will later be used for a print command. The _exact_ location is part of what's giving me trouble-- I've tried wildcards in the path (because of the timestamp), but it never works. — atomickiwi, Aug 23 '16 at 10:43

Comparing files in two different folders with string variable names

0 Answers0