Dir Function Sanity Check

[rjvega]

Hi, I have a bit of code where the user enters a list of document numbers and
it goes out to a directory and copies the related PDF files to another
folder. It works fine, but I need it to go through one more check. Every
now and then, those who create the files sometimes have to create a second
file. For example, there may be a 5000064218.pdf AND a 5000064218 (2).pdf.

There is no way to tell without opening the PDF which one is the one they
will need, so what I want my code to do is see if there are multiples and
just identify those on the spreadsheet. The user will then have to manually
decide which file they need.

I created a check that seems to work, but I'm not sure if it will be a
robust test or if I just got lucky with the test. Here's what I have so far.

The document numbers are in column B and the source path and destination
path are in cells D2 and D4 respectively. n is just a counter that
increments the row. mdrow is the last row. tempa and tempb are just string
variables.

Do Until n = mdrow
srcfile = Range("D2").Value & "\" & Range("b" & n).Value
destfile = Range("D4").Value & "\" & Range("b" & n).Value & ".pdf"
tempa = (Dir(srcfile & "*"))
tempb = (Dir(srcfile & ".pdf"))
if tempa = tempb then
FileCopy srcfile, destfile
else
range("C" & n) = "Duplicate"
endif
n = n + 1
Loop

Now, with this code, tempb has been reading the (2) file if it exists and
the base file if no (2) exists. tempa is always reading the base file. So
it seems to be working, but I'm not sure if it will always work.

Thanks in advance.

 

[Rick Rothstein \(MVP - VB\)]

I am pretty sure what you have it robust but, based on your description of
what the file names look like, I would have done it slightly different
(saving a Dir call and the two temp variables in the process)...

Do Until n = mdrow
srcfile = Range("D2").Value & "\" & Range("b" & n).Value
destfile = Range("D4").Value & "\" & Range("b" & n).Value & ".pdf"
If Dir(srcfile & "*") Like "*(*)*" Then
Range("C" & n) = "Duplicate"
Else
FileCopy srcfile, destfile
End If
n = n + 1
Loop

Rick

 

[rjvega]

Thanks Rick.

There are occasional instances where they didn't name the file in the same
format and did a -2 or something like that. I know... we lack some
standards, but we're working on it!