Consulting

Results 1 to 7 of 7

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

  1. #1

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

    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

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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.

  3. #3
    Check the last few characters e.g
    If Right(UCase(objAtt.FileName), 4) = ".XLS" Then
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    Quote Originally Posted by Logit View Post
    .
    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..

  5. #5
    Quote Originally Posted by gmayor View Post
    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
    Last edited by johngalvin; 10-05-2019 at 12:28 AM.

  6. #6
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    Hello
    Thanks for your reply.

    It works !


    Quote Originally Posted by gmayor View Post
    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
    Last edited by johngalvin; 10-05-2019 at 01:21 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •