DIR function

[EthanM193]

Hello

I am fairly new to VBA, however I have an excel spreadsheet that contains various addresses.

Each address has a folder . For each address there should be 3 files. The files have their own abbreviations I.e the picture file has PIC at the end. Each file is named in the format address_abbreviation I.e 1ABCStreet_PIC.

I have been trying to identify if any files are missing using couple of options:

1. Function FileExists(sFile As String)
sPath = “Folder location” & sFile & “_PIC.pdf”
FileExists = DIR(sPath) <> “”
End Function

Then for each address in the spreadsheet I have been inserting the formula =If(FileExists(A1), “y”, “n”). Where A1 has the address in the same format as that the files have been named.

It always returns “n” and I am unsure where I am going wrong.

2. Sub DIR()
Dim Filename As String
Filename=“Folder location” & A1 & “_PIC.pdf”
If Filename = VBA.Constants.vbNullString then
Sheet1.Range(“B1”).Value = “n”
Else
Sheet1.Range(“B1”).Value=“y”
End If
End sub

I believe there is an issue with trying to specify the filename using data from excel cells, but that is just a guess and any help would be much appreciated.

[Keebellah]

Hi, welcome to the forum, the return value of you macro is a Boolean, either TRUE or FALSE
and not y or n
You will have to create that yourself

Please Login or Register  to view this content.

[EthanM193]

So I updated the VBA function to
FunctionExists (sFile As String) As Boolean
sPath = “Folder location” & sFile & “PIC.pdf”
If FileExists = DIR(sPath) <> “” Then
FileExists = True
Else
FileExists = False
End If
End Function

The formula in the worksheet is now just = FileExists(A1)

But it returns #Value!

😒

[Keebellah]

Sure it does, you mistyped the function check your syntax FUNCTIONEXITS ?????
You didn't need to change the code

[Keebellah]

What are the values for
“Folder location”
sFile & “_PIC.pdf”
?

[Keebellah]

This is the synxat

Please Login or Register  to view this content.

And how it looks in the formula