Is it possible??? Using Excel to search files on a desktop folder

Good morning,

This is a good one and I'm not even sure if it's possible... Solving this issue would really make year!!!! not kidding.

Here's my issue, I'm working with one main Excel file for the whole fiscal year. This file is mainly used to track paid and unpaid invoices. (Column D = invoice #)

Considering that all the PDF invoices are saved in a main folder (each PDF files name are the invoice # mentionned in column D), is there a way or a formula that Excel could seach in the invoice folder and insert "yes" if the invoice is available in that fodler?

Thanks in advance

Dan

thanks for your help, but I'm familiar enough with VBA, but I will try to figured-it out

my bad, herE's the file and here's my path

U:\2016 - 2017

Thanks in advance

Here is a solution using a UDF called InvoiceFileExists. The values you see are leftover from my test--I do not have a U drive. If they do not automatically refresh to match your environment, you may need to force them to recalculate.

as recommended, I will ask you another question here.

I have tried to copy/paste your VB Code into a different spreadsheet because I want to do the same thing and the path is in red.¸

Is it possible to simply copy/paste your coding into another spreadeet?

Originally Posted by philion_91

the path is in red.

Do you mean this line of code is red:

Please Login or Register  to view this content.

That indicates a syntax error, but should not happen if you are simply copying and pasting the entire code. Are you sure you didn't delete a character in the process by accident?

You can copy all the code from the new location and paste it here for review. In this case even a screenshot would be OK, although that would take you more steps.

The first print screen is from the file that you have provided me with (worksvery well)

The second print screen is the error message that I got after I copied-pasted the coding into a different spreadsheet.

3th prin screet is the pasted cosing

Public Const pathroot As String = "U:\FAS-SFA\CAANCS-CAMSRN\CA-CM\NAS-SCAC\COMPTES PAYABLES\OGD\2016 - 2017\Invoices Only\"

Finnally, the 4th print screen is the formula that I have recreated for your the invoice #.

The error message says what's wrong. My code was in Module1 but you pasted it into a Worksheet module. You cannot have a Public Const in a Worksheet module. Fortunately, it doesn't need to be Public. You can just delete the word Public in the Const declaration and you should be ready to go.

nice, the path is correct no, but unfortunately there's another thing.

I'm still getting an error instead in column L, where I have added formula =InvoiceFileExists(D2)

Do you know why?

Once again, thanks for your help and your patience, I really want this to work and unfortunately, there's nobody around to help me with VB.