If File Exists

Posted on 2009-12-17
Medium Priority
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

Cory Vandenberg earned 500 total points
ID: 26074781
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

t0t0 earned 500 total points
ID: 26128943
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

862 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