VBA to open Excel Workbook if file exists, and open a different if it does not-CodePudding

I have spent my whole morning on this and cannot get it working properly. A simple Excel userform was created asking for a filename. If the file exists in the directory I want it to open. If it does not exist I want a "template" file opened instead. I have the does not exist working properly, however cannot get the "does exist" part working. Please help.

Private Sub CmdEnter_Click()
Dim Path As String
Dim File As String
 
Path = Range("Search!B1")
File = TxtOrder.Value
    
'If File exists then open.
If Dir(Path & File & ".xlsm") = Path & File & ".xlsm" Then
    Workbooks.Open FileName:=Path & File & ".xlsm"
    
'If File does not exist then open.
ElseIf Dir(Path & File & ".xlsm") = Error Then
    Workbooks.Open FileName:=Path & "QCSFormTrial.xlsm"
    
End If

'Close Dialog and Close Workbook
Workbooks("QCSLaunch.XLSM").Close SaveChanges:=False

End Sub

CodePudding user response：

Please, try this way:

Private Sub CmdEnter_Click()
 Dim Path As String, File As String, wb As Workbook
 
 Path = Range("Search!B1")
 File = TxtOrder.value
    
 'If File exists then open.
 If dir(Path & File & ".xlsm") <> "" Then
    Set wb = Workbooks.Open(Path & File & ".xlsm")
 Else 'else, open the other one:
    Set wb = Workbooks.Open(Path & "QCSFormTrial.xlsm")
 End If

 Stop 'check if the workbook has been open and press F5 to let code finishing
 wb.Close SaveChanges:=False
End Sub

CodePudding user response：

The issue is that Dir(Path & File & ".xlsm") = Path & File & ".xlsm" is basically saying does the folder path I named equal the folder path I named. The path isn't actually directed at the actual folder in way that will open it.

Try this: https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/

Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim fileName As Variant
fileName = Dir("C:\Users\marks\Documents\")

While fileName <> ""
    
    'Insert the actions to be performed on each file
    'This example will print the file name to the immediate window
    Debug.Print fileName

    'Set the fileName to the next file
    fileName = Dir
Wend

End Sub

Or, you can remove the If Then and directly open the file. If the file exists, it will open, if not, it will error. You can use error handling then continue.