How to target just .XLS extension and not implicitly .XLSX ?

[johngalvin]

Hello,

This is what I've tried, but if the document is .xlsx it will be taken because the code wants at least .xls, hence .xlsx is considered unfortunately, because it contains .xls
Is there any way to target just .xls?

Many thanks !

John


If Dir(stFileName) = "" Then
If UCase(objAtt.DisplayName) Like "*.XLS" Then
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
End If

[Logit]

.
Remove the LIKE



If Dir(stFileName) = "" Then
If UCase(objAtt.DisplayName) = "*.XLS" Then
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
End If



This part UCase(objAtt.DisplayName) may need to be changed as well but I'm not certain at this point.

[gmayor]

Check the last few characters e.g

If Right(UCase(objAtt.FileName), 4) = ".XLS" Then

[johngalvin]

.
Remove the LIKE



If Dir(stFileName) = "" Then
If UCase(objAtt.DisplayName) = "*.XLS" Then
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
End If



This part UCase(objAtt.DisplayName) may need to be changed as well but I'm not certain at this point.

Hello
Thank you for your message.
I replaced the Like with =, but nothing changed, it still grabs .XLSX, I'm trying to make it look just for .XLS, but since .XLSX contains .XLS, it grabs the .XLSX too unfortunately..

[johngalvin]

Check the last few characters e.g

If Right(UCase(objAtt.FileName), 4) = ".XLS" Then

Hello,
Thanks for your reply. I tried the above code but it still grabs the .XLSX, perhaps because if it checks for the last 4 characters to be .XLS, the .XLSX also contains .XLS hence it grabs the .XLSX

I was thinking of doing sort of a MatchCase if possible or an If statement to do nothing (Exit Sub) on .XLSX



Edit: Tried this for the MatchCase but didn't work

If Dir(stFileName) = "" Then
If UCase(objAtt.DisplayName) = ".XLS" and UCase(objAtt.DisplayName) <> ".XLSX" Then
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
If InStr(objAtt, "Media") > 0 Then
objAtt.SaveAsFile saveFolder & objAtt.DisplayName
End If
End If

[gmayor]

The right function only reads the last four characters. The last four characters of the xlsx file are xlsx and not .xls
It is easy enough to demonstrate. Select a message and run the test macro below


Sub Test()
Dim olMsg As MailItem
On Error Resume Next
Set olMsg = ActiveExplorer.Selection.Item(1)
CheckAttachments olMsg
lbl_Exit:
Exit Sub
End Sub


Sub CheckAttachments(olItem As MailItem)
Dim objAtt As Attachment
For Each objAtt In olItem.Attachments
MsgBox Right(UCase(objAtt.fileName), 4)
If Right(UCase(objAtt.fileName), 4) = ".XLS" Then
MsgBox objAtt.fileName
End If
Next objAtt
lbl_Exit:
set objAtt = Nothing
Exit Sub
End Sub

[johngalvin]

Hello
Thanks for your reply.

It works !



The right function only reads the last four characters. The last four characters of the xlsx file are xlsx and not .xls
It is easy enough to demonstrate. Select a message and run the test macro below


Sub Test()
Dim olMsg As MailItem
On Error Resume Next
Set olMsg = ActiveExplorer.Selection.Item(1)
CheckAttachments olMsg
lbl_Exit:
Exit Sub
End Sub


Sub CheckAttachments(olItem As MailItem)
Dim objAtt As Attachment
For Each objAtt In olItem.Attachments
MsgBox Right(UCase(objAtt.fileName), 4)
If Right(UCase(objAtt.fileName), 4) = ".XLS" Then
MsgBox objAtt.fileName
End If
Next objAtt
lbl_Exit:
set objAtt = Nothing
Exit Sub
End Sub