If File Exists

Posted on 2009-12-17
Last Modified: 2012-05-08
Have a folder X:\TEST with 1000s of PDFs. (25125143.PDF, 234132534.PDF, and so on)

Have an excel sheet with filenames (without the ".PDF") in col A1

Want a script that looks through the excel col A1, and then if file exists in folder X:\TEST to COPY to a diff folder  X:\FOUND

Question by:super786
    LVL 18

    Accepted Solution

    Try the following

    Sub CopyPDFs()
      Dim fromDirectory as String
      Dim toDirectory as String
      Dim cel as Range
      fromDirectory = "X:\TEST"
      toDirectory = "X:\FOUND"
      For Each cel in Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If FileExists(fromDirectory & "\" & cel & ".pdf") Then FileCopy fromDirectory & "\" & cel & ".pdf", toDirectory & "\" & cel & ".pdf"
      Next cel
    End Sub
    Public Function FileExists(fileName As String) As Boolean
         FileExists = (VBA.dir(fileName) > "")
    End Function

    Open in new window

    LVL 16

    Assisted Solution

    To use batch scripting, you would simply need to save your XLS file as a CSV file (using SaveAs) and then run the code below.

    Suppose you name your CSV file FILENAMES.CSV, make sure you save it in the same folder as this batch file, then run it.

    @echo off
    for /f "tokens=* delims=," %%a in ('type filenames.csv') do (
       if exist "x:\test\%%a.pdf" (
          copy "x:\test\%%a.pdf" "x:\found\"

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now