Wildcards in VBA

[rangersedge]

I'm trying to use a code to check if a file exists. The code I have already works and gives a MsgBox telling if it exists. It gets the file address through a query that combines multiple fields. This works great when I have the file type entered in the proper field. How do I get this to work if I don't know the file type?

The b variable was meant to find the file without the file type as all my file types are only 3 characters long.

Dim FilePath As String
Dim TestStr As String

Dim a As String
Dim b As String

a = Me.Address
b = LResult = Left(Me.Address, Len(Me.Address) - 3)

FilePath = a

TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
MsgBox "File does not exist"
Else
MsgBox "File exist"
End If

 

[llkhoutx]

Test the file type too, i.e. return the name and extension, stiriping off each as needed.

 

[rangersedge]

How do I do that?

 

[rangersedge]

That would be useful since my next task is to have it automatically input the file type.

 

[rangersedge]

This almost works. It will find the file if I have no extension but not if the file extension is in the address. Also the TestStr returns only the file name and extension into the address box and not the full file path. The FileType doesn't fill with anything. I don't think parts b and c are looking at the right address.... maybe?

Dim FilePath As String
Dim TestStr As String

Dim a As String
Dim b As String
Dim c As String

a = Me.Address
b = Left$(Me.Address, Len(Me.Address) - InStrRev(Me.Address, "."))
c = Split(Me.Address, ".")(UBound(Split(Me.Address, ".")))

FilePath = a

TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath & "*")
On Error GoTo 0
If TestStr = "" Then
MsgBox "File does not exist"
Else
MsgBox "File exist"
Me.Address = TestStr
Me.Collected = 1
Me.FileType = c

End If

 

[essaytee]

Use the wildcard character "*" as the extension and simply loop through the results. First time through you will have to provide the full path.

Dir function here.

Steve.

 

[gemma-the-husky]

what does the file type have to do with determining whether the file exists?

get the file type using instrev. This will work. easier than determining whether your split syntax works.

filetype = mid(address,instrrev(address,".")+1)