VBA Question - On Error Resume Next with Two Arguments - Excel 2010

[Johnny Thunder]

Hello All,

I am working on a project that requires a user to push a macro button that will open a file specified in a drop down list. The file may be .XLS or a .XLSM and there would be no way for the user to know this so I need the code to look and see if it is a XLS and IF Not then Open the XLSM version of the file.

Here is my code but it is not working? Any ideas would help even if the way I am doing this can be simplified with different code.

varCellvalue = Sheets("Main").Range("B24").Value
varcell = Sheets("Main").Range("B20").Value
       
   Dim Destbook As Workbook
       
  On Error Resume Next
    Set Destbook = Workbooks(varCellvalue & ".xlsm")
    On Error GoTo 0
   
 If Destbook Is Nothing Then
      Set Destbook = Workbooks.Open("\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xlsm")

    On Error GoTo 0

    If Destbook Is Nothing Then
      Set Destbook = Workbooks.Open("\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xls")
    
    Else
        
      Destbook.Activate
    
    End If
      
    Sheets("ODM").Select
    Range("H11").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

 

[Jerry Sullivan]

Hi Johnny Thunder,

Here's one way to code that....

Sub Test()
    Dim Destbook As Workbook
    Dim varCellvalue As Variant
    Dim sNameFound As String
 
    
    Const sPath As String = 
        "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\"

    
    varCellvalue = Sheets("Main").Range("B24").Value

        
    On Error Resume Next
    Set Destbook = Workbooks(varCellvalue & ".xls")
    If Err.Number <> 0 Then _
        Set Destbook = Workbooks(varCellvalue & ".xlsm")
    On Error GoTo 0

    
    If Destbook Is Nothing Then
        sNameFound = Dir(sPath & varCellvalue & ".xls*")
        If sNameFound = vbNullString Then
            MsgBox "No files found matching: " & sPath & varCellvalue & ".xls*"
            Exit Sub
        Else
            Set Destbook = Workbooks.Open(sPath & sNameFound)
        End If
    Else
        Destbook.Activate
    End If

 
End Sub

 

[Johnny Thunder]

Hi Johnny Thunder,

Here's one way to code that....

Sub Test()
    Dim Destbook As Workbook
    Dim varCellvalue As Variant
    Dim sNameFound As String
 
    
    Const sPath As String = 
        "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\"

    
    varCellvalue = Sheets("Main").Range("B24").Value

        
    On Error Resume Next
    Set Destbook = Workbooks(varCellvalue & ".xls")
    If Err.Number <> 0 Then _
        Set Destbook = Workbooks(varCellvalue & ".xlsm")
    On Error GoTo 0

    
    If Destbook Is Nothing Then
        sNameFound = Dir(sPath & varCellvalue & ".xls*")
        If sNameFound = vbNullString Then
            MsgBox "No files found matching: " & sPath & varCellvalue & ".xls*"
            Exit Sub
        Else
            Set Destbook = Workbooks.Open(sPath & sNameFound)
        End If
    Else
        Destbook.Activate
    End If

 
End Sub

Awesome, worked perfectly! Thanks!