Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir Function Sanity Check
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir Function Sanity Check
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir Function Sanity Check
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! "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple users on workbook, but no shared drive, how to maintain sanity? | Excel Discussion (Misc queries) | |||
if end if sanity check... | Excel Programming | |||
Filtering in a view (sanity check) | New Users to Excel | |||
splitting names can't see whats going wrong (Save My Sanity ) | Excel Worksheet Functions | |||
splitting names can't see whats going wrong (Save My Sanity ) | Excel Worksheet Functions |