I do not understand why this does not work
xSource = vrtSelectedItem '<<== C:\Users\Me\Desktop\Document01.pdf
Set FSO = VBA.CreateObject("Scripting.FileSystemObject")
If Not InStr(xSource, ".jpg") Or Not InStr(xSource, ".bmp") Or Not InStr(xSource, ".png") _
Or Not InStr(xSource, ".tif") Or Not InStr(xSource, ".tga") Or Not InStr(xSource, ".jpeg") _
Or Not InStr(xSource, ".doc") Or Not InStr(xSource, ".pdf") Or Not InStr(xSource, ".rtf") _
Or Not InStr(xSource, ".htm") Or Not InStr(xSource, ".html") Or Not InStr(xSource, ".txt") _
Or Not InStr(xSource, ".docx") Or Not InStr(xSource, ".tdm") Or Not InStr(xSource, ".wri") _
Or Not InStr(xSource, ".xls") Or Not InStr(xSource, ".xlsx") Or Not InStr(xSource, ".xlsm") _
Or Not InStr(xSource, ".ods") Or Not InStr(xSource, ".odt") Then
MsgBox "File type not allowed"
Exit Sub
Else
.....
Although the file contains .pdf, I get MsgBox "File type not allowed"! This Happens also with all other file types I listed to actually exclude them from the error message! Can anyone give me some advice? Thanks
TL;DR: InStr
doesn't return a Boolean
, but rather a Variant
(Long
) specifying the position of the first occurrence of one string within another.
Simplifying to explain the problem:
xSource = "C:\Users\Me\Desktop\Document01.pdf"
Debug.Print InStr(xSource, "pdf")
Debug.Print Not InStr(xSource, "pdf")
Debug.Print CBool(Not InStr(xSource, "pdf"))
returns
32
-33
True
InStr
does not return a boolean, rather the position of the first occurrence of one string within another. Rather than using Not
, normally one would check if the result of InStr
is > 0
to determine if a match was found.
Using Not
on a numeric expression is performing bitwise negation, which as demonstrated above causes the result of a match to ultimately evaluate to True
. In fact, no match also evaluates to True
(CBool(Not(0))
returns True
).
Here's an alternate way to do this:
Private Function IsValidFileType(ByVal filePath As String) As Boolean
Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject
Dim extension As String
extension = FSO.GetExtensionName(filePath)
Select Case extension
Case "jpg", "bmp", "png" '< and so on
IsValidFileType = True
Case Else
IsValidFileType = False
End Select
End Function