Check if workbook exists

[lifeson]

Morning folks
Another week begins in work world

I have the following code that opens another workbook when the main workbook opens:
[VBA]Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wb As String
Dim path As String
Dim fName As String
path = ThisWorkbook.path
wb = "\MyData.xls"
fName = path & wb

Workbooks.Open Filename:=fName


End Sub[/VBA]
How do I check that the workbook exits before opening?

[Bob Phillips]

[vba]

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wb As String
Dim path As String
Dim fName As String
path = ThisWorkbook.path
wb = "\MyData.xls"
fName = path & wb

If Dir(fName, vbNormal) <> "" Then Workbooks.Open Filename:=fName

End Sub
[/vba]

[lifeson]

Thats what I was looking for but I modified it to give a message if the file does not exist

[vba]If Dir(fName, vbNormal) = "" Then
msg = "The file: " & vbNewLine
msg = msg & fName & vbNewLine
msg = msg & "Does not exist." & vbNewLine
msg = msg & "You will not be able to view any stored notes"
MsgBox (msg)

Else
Workbooks.Open Filename:=fName
End If[/vba]

[Bob Phillips]

Line continuations work better (IMO)

[vba]

If Dir(fName, vbNormal) = "" Then
msg = "The file: " & vbNewLine & _
fName & vbNewLine & _
"Does not exist." & vbNewLine & _
"You will not be able to view any stored notes"
MsgBox (msg)

Else
Workbooks.Open Filename:=fName
End If
[/vba]

[lifeson]

Thanks for the tip

I picked up the message formating from the "dummies" book
I must admit though I do find it easier to understand

[Bob Phillips]

Unfortunately, the VBA formatter in VBAX changes why I actually entered, mine reads better IMO. It would bd something like

If Dir(fName, vbNormal) = "" Then
....msg = "The file: " & vbNewLine & _
.............fName & vbNewLine & _
............."Does not exist." & vbNewLine & _
............."You will not be able to view any stored notes"
....MsgBox (msg)

This makes it easier to understand the format as well.

[unmarkedhelicopter]

I agree with Bob about making things easier to read (especially when I'm trying to find a mistake in MY code !) but ...
I don't like vbNewLine, I prefer vbCrLf, probably from my scripting days.
It's shorter and it reminds me to use vbCr sometimes and cbLf too.
In case you are wondering it's from printer control (and old screen (console) control too) Cr = Carriage Return and Lf = Line Feed
So Three lines of text in 1 cell becomes "First Line" & vbLf & "Second Line" & vbLf & "Third Line" etc.

[Bob Phillips]

The only thiung I would say in its defence UMH is that, apparently, vbNewline works on Macs as it does on Windows, so making it more platform independent (that is my understanding, Mac'ers can correct me if wrong).

[TheAntiGates]

Stylistic remark. I have wavered on this, but lately feel that ampersands on the continuations are a quicker mental cue that a continuation is in play. The trigger (the underscore) is off on the right, and in visually following code you scan down the left, so you get more flow information this way. At least, that's my latest view.

By the way, as to your earlier indenting woe, remember that you have ye olde [ code ] tags, too, as it sounds like you wanted this vertical alignment

msg = "The file: " & vbNewLine & _ 
    fName & vbNewLine & _ 
    "Does not exist." & vbNewLine & _ 
    "You will not be able to view any stored notes"

What I proposed is

msg = "The file: " & vbNewLine _ 
    & fName & vbNewLine _ 
    & "Does not exist." & vbNewLine _ 
    & "You will not be able to view any stored notes"

because you mentally will immediately realize that those aren't standalone commands.

Now just fronting the ampersand is great for the "long line cure." Yet when you have newlines (I also use vbcrlf, BTW),

msg = "The file: " & _ 
    vbNewLine & fName & _ 
    vbNewLine & "Does not exist." & _ 
    vbNewLine & "You will not be able to view any stored notes"

or

msg = "The file: " _ 
    & vbNewLine & fName _ 
    & vbNewLine & "Does not exist." _ 
    & vbNewLine & "You will not be able to view any stored notes"

Woohoo - those last two really self-document,IMO. Not as great vertical alignment, but great indication of flow.

[TheAntiGates]

Back to OP - Some time long ago I was persuaded to check for existence by opening and flip a Boolean in the error handling, a la[vba]function FileExists(fname as string) as boolean
dim IsOpen as boolean
on error goto foo
open fName for input as #1
close #1
FileExists=false
exit function
FileExists=true
End function[/vba] I haven't done speed tests but from the looks of it I'm not encouraged that it's quicker. AAR it does work.

[unmarkedhelicopter]

I have a lot of screen space and unless I am required to shorten lines to a specific standard value, I use the width I've got. Even so, continuation characters are still required and I like your idea of making it obvious that this line relates to a previous line. Thanks.

Bob, Mac compatibility, good point, I've often wondered how much code you'd need to tweak to get to run on a Mac (especially dates ) but I can't help but feel this is just plain laziness on behalf of MS as the VB we write is compiled even if on the fly, so on a Mac platform the compiler should read vbCrLf as vbNewLine, if that is an issue.
But I would like to hear some Mac users views on this unfortunately the only one I know is Shades and he rarely comments on this issue.