Open Excel File Unknow Extension

[motabrasil]

Hi,
I have a code that capture several files saved in a specific folder by users. However, even asking the users to save as .xlsx files, some files were saved as .xls and the macro cannot find them.
Is there a way to prevent this error, making the code open any extension saved (.xls or .xlsx)?

Example: sFile = ActiveWorkbook.Path & "\" & "FileName*"

Thanks in advance
Motabrasil

 

[SirJB7]

Hi, motabrasil!
And the code is...?
Regards!

 

[motabrasil]

Hi SirJB7
See described below the portion that check the files. We have the same loop to check 30 different files every day, based on drop down menu.
It works fine if those files are saved as .xlsx
However, when any file is saved as .xls the code stop with the message "...was not found".
Someone suggest me to add a new line for each loop setting all files twice. One as .xls and another as .xlsx
But I thing there is a better and intelligent way to solve this.

Thanks in advance

***

If ws1.Cells(3, "G").Value = "Filename1" Then
sFile = ActiveWorkbook.Path & "\" & "Filename1.xlsx"
If Dir(sFile) = Empty Then
MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
Exit Sub
Else
Set wbsh1 = Application.Workbooks.Open(sFile)
End If
ElseIf ws1.Cells(3, "G").Value = "Filename2" Then
sFile = ActiveWorkbook.Path & "\" & "Filename2.xlsx"
If Dir(sFile) = Empty Then
MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
Exit Sub
Else
Set wbsh2 = Application.Workbooks.Open(sFile)
End If
End If

 

[SirJB7]

Hi, motabrasil!

Try this code:

    If ws1.Cells(3, "G").Value = "Filename1" Then
        sFile = ActiveWorkbook.Path & "\" & "Filename1.xls*"
        If Dir(sFile) = Empty Then
            MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
            Exit Sub
        Else
            sFile = Dir(sFile)
            Set wbsh1 = Application.Workbooks.Open(sFile)
        End If
    ElseIf ws1.Cells(3, "G").Value = "Filename2" Then
        sFile = ActiveWorkbook.Path & "\" & "Filename2.xls*"
        If Dir(sFile) = Empty Then
            MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
            Exit Sub
        Else
            sFile = Dir(sFile)
            Set wbsh2 = Application.Workbooks.Open(sFile)
        End If
    End If

Not tested but I'd give it a try anyway.

Regards!

PS: Next time you post code do follow the instructions over the reply text box. Or use the proper icon from the ribbon, 5th from the right.

 

[motabrasil]

Hi SirJB7
Sorry for my latest reply. I was in a international business travel.
I tested your suggestion and worked perfect.

Thank you very much for your support.

Regards
A Mota

 

[SirJB7]

Hi, motabrasil!
Thanks for the acknowledge, better later than never
And nothing to worry about... got the address to send a few boxes of Garotos?
Regards!