Does a path exist?

[Daxton A.]

My problem is that if the folder doesn't exist, I get the "Error Msg". But what I'm
trying to figure out is how to bypass the error msg by popping a "Find the
correct correct path or a create the path box. Any comments?

ActiveWorkbook.SaveAs FileName:= _
"S:\Daily Production Press Efficiency\" & "EFFICIENCY REPORTS " & _
NumYear & "\" & NumMonth & "-" & Right(NumYear, 2) & _
" PRODUCTION EFFICIENCY REPORT.xls", FileFormat:= _
xlNormal, & _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

[Ken Puls]

Hi Daxton!

From a forthcoming KB entry (in approval process right now)

Function FileOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
'               or directory exists, false if not.
'File usage   : Provide full file path and extension
'Path usage   : Provide full file path only (accepts with/without trailing "\")
Dim sTemp As String
'Ignore errors to allow for error evaluation
    On Error Resume Next
    sTemp = GetAttr(PathName)
'Check if error exists and set response appropriately
    Select Case Err.Number
        Case Is = 0
            FileOrDirExists = True
        Case Else
            FileOrDirExists = False
    End Select
'Resume error checking
    On Error GoTo 0
End Function

So you could use it like so:

Sub TestIt()
'Macro Purpose: To test the FileOrDirExists function
'Only included to demonstrate the function.  NOT required for normal use!
Dim sPath As String
'Change your directory here
sPath = 
"S:\Daily Production Press Efficiency\" & "EFFICIENCY REPORTS " & _
NumYear & "\"  
'Test if directory or file exists
If FileOrDirExists(sPath) Then
ActiveWorkbook.SaveAs FileName:= _ 
spath & NumMonth & "-" & Right(NumYear, 2) & _
 " PRODUCTION EFFICIENCY REPORT.xls", FileFormat:= _ 
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ 
, CreateBackup:=False 
Else
    MsgBox sPath & " does not exist."
End If
End Sub

HTH,

[Bob Phillips]

My problem is that if the folder doesn't exist, I get the "Error Msg". But what I'm
trying to figure out is how to bypass the error msg by popping a "Find the
correct correct path or a create the path box. Any comments?

ActiveWorkbook.SaveAs FileName:= _ 
"S:\Daily Production Press Efficiency\" & "EFFICIENCY REPORTS " & _ 
NumYear & "\" & NumMonth & "-" & Right(NumYear, 2) & _ 
" PRODUCTION EFFICIENCY REPORT.xls", FileFormat:= _ 
xlNormal, & _ 
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ 
, CreateBackup:=False

Create it.

Dim sFolder As String
Dim sFile As String
    sFolder = "S:\Daily Production Press Efficiency\" & "EFFICIENCY REPORTS " & NumYear
    sFile = NumMonth & "-" & Right(NumYear, 2) & " PRODUCTION EFFICIENCY REPORT.xls"
    On Error Resume Next
        MkDir sFolder
    On Error GoTo 0
    ActiveWorkbook.SaveAs Filename:=sFolder & "\" & sFile, _
                          FileFormat:=xlNormal, _
                          Password:="", _
                          WriteResPassword:="", _
                          ReadOnlyRecommended:=False, _
                          CreateBackup:=False

[Daxton A.]

This message has been solved

[Bob Phillips]

Dim sTemp As String
'Ignore errors to allow for error evaluation
    On Error Resume Next
    sTemp = GetAttr(PathName)

Ken,

GetAttr returns an integer, not a string.

[Ken Puls]

GetAttr returns an integer, not a string.

Well, now that is a curious thing... the method works anyway. I'm going to have to play with that one a bit.

Thanks!

[Ken Puls]

Hello,

I ran through it, and it works because you can read an integer into a string as text. It caught me a little off guard, only because I'm used to seeing a Type Mismatch error when you use the wrong data type.

Because the function only tests if it was able, and doesn't actually use the value, it actually doesn't really affect it much in this case. Ideally, though, sTemp should actually be defined as an Integer (and probably with a name like iTemp instead.)

Thanks for pointing it out, xld!

[Bob Phillips]

I ran through it, and it works because you can read an integer into a string as text. It caught me a little off guard, only because I'm used to seeing a Type Mismatch error when you use the wrong data type.

Because the function only tests if it was able, and doesn't actually use the value, it actually doesn't really affect it much in this case. Ideally, though, sTemp should actually be defined as an Integer (and probably with a name like iTemp instead.

Yes, I am not surprised it doesn't complain, as it would seem that VBA casts it into a string, because it can (unlike trying to cast "ABC" into a number). I pointed it out as because it does work doesn't mean that you should use it (unless you are doing so to get some undocumented advantage) as it may not work in a later version where rules may be more rigidly enforced.

And,it becomes more relevant when using GetAttr for what it was intended for, to get file attrributes such as hidden flags.

[Ken Puls]

Yes, I am not surprised it doesn't complain, as it would seem that VBA casts it into a string, because it can (unlike trying to cast "ABC" into a number). I pointed it out as because it does work doesn't mean that you should use it (unless you are doing so to get some undocumented advantage) as it may not work in a later version where rules may be more rigidly enforced.

And,it becomes more relevant when using GetAttr for what it was intended for, to get file attrributes such as hidden flags.

Absolutely agree with you on all points.

It will be fixed in the KB entry.

[sheeeng]

Absolutely agree with you on all points.

It will be fixed in the KB entry.

Great...
Keep it up..

[Ken Puls]

Just for reference... Check if directory or file exists is now updated in the KB.