Vba to search in a folder for a file name that is either a pdf or excel work book

[Pr0x1mo]

Ok so i was given a list of Invoices (about a thousand) and i need to search each one in a subfolder to see if they were saved or not. If they were saved they are saved as the invoice number which makes it easier.

So i wanted to create something where when i put an invoice number, say 2142072 into A1, it searches in this directory for a pdf or excel sheet titled 2142072:

S:\Everyone\Invoices Sent Electronically\


And if it does it copies that invoice number into a column titled "Found" or if it doesn't it puts it in a column called "not found"

So every time i enter in a new invoice number into A1, it'll place that invoice number into the Found or not found column beneath the invoice it found or didn't find already.

Is this possible?

[BellyGas]

yes.

The quickest and easiest way to determine if a file exists is to run a getattr command on it (to retrieve the file attributes). If the command errors then the file doesn't exist.

ie

Please Login or Register  to view this content.

[Pr0x1mo]

look below

[Pr0x1mo]

yes.

The quickest and easiest way to determine if a file exists is to run a getattr command on it (to retrieve the file attributes). If the command errors then the file doesn't exist.

ie

Please Login or Register  to view this content.

Stupid question but is this exactly what i copy and paste onto vba?

[ImranBhatti]

INSERT A MODUALE IN VBA AND PASTE THIS CODE THERE

Please Login or Register  to view this content.

Assuming you will put invoice number in Column A
type this formula in B adjacent to the invoice number

=IF(FileExistspdf(A1),"Found","Not found")

DRAG

[Pr0x1mo]

Ok, another dumb question, every time i put this in a module, and try to run it, it just pops up a blank message box asking me to select a macros i want to run.... do you run modules a different way or something?

[ImranBhatti]

Ok, another dumb question, every time i put this in a module, and try to run it, it just pops up a blank message box asking me to select a macros i want to run.... do you run modules a different way or something?

If you are talking about post#6 code then
You don't have to run it at all.just save it there and close the vbe .
You need to put the formula in the cell adjacent to the invoice number on the sheet.the formula is

=IF(FileExistspdf(A1),"Found","Not found")

Just like any built in formulas like sum ,sumif etc.