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

Question

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

What string does Path contain? If it contains a string ending in "\", you should use If Dir(Path & File & ".xlsm") <> "" Then. Instead of ElseIf Dir(Path & File & ".xlsm") = Error Then use just Else. — FaneDuru
– FaneDuru, Commented Nov 26, 2021 at 20:19
It does end in "\" Right now this is just trying to get a workable version so I made that cell editable so it could be changed easily. — Wapalo
– Wapalo, Commented Nov 26, 2021 at 20:22
Then try implementing what I recommended. Anyhow Dir(Path & File & ".xlsm") = Error does not have any meaning in VBA. Dir() returns a path or an empty string. Never a error and even if it should do so, that way of checking for error does not exist in VBA. — FaneDuru
– FaneDuru, Commented Nov 26, 2021 at 20:23
In the meantime, I posted an answer. It will set a workbook (wb) which can be any of the two. Your code will raise an error if the first condition is True... — FaneDuru
– FaneDuru, Commented Nov 26, 2021 at 20:30
How is "QCSLaunch.XLSM" related to the code? Is it the workbook containing the cell with the path? Is it the workbook containing this code? Why are you closing it? — VBasic2008
– VBasic2008, Commented Nov 27, 2021 at 10:17

FaneDuru · Accepted Answer · 2021-11-26 20:29:30Z

1

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

answered Nov 26, 2021 at 20:29

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brett · Accepted Answer · 2021-11-26 20:31:49Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related