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?:dunno




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?:dunno




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. :yes

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. :yes

It will be fixed in the KB entry.

[sheeeng]

Absolutely agree with you on all points. :yes

It will be fixed in the KB entry.

Great...:thumb
Keep it up..

[Ken Puls]

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