PDA

View Full Version : Solved: Does a path exist?



Daxton A.
07-13-2005, 01:34 PM
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
07-13-2005, 01:44 PM
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,

xld
07-13-2005, 01:44 PM
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.
07-13-2005, 02:25 PM
This message has been solved

xld
07-13-2005, 05:00 PM
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
07-13-2005, 10:32 PM
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
07-13-2005, 10:54 PM
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!

xld
07-14-2005, 10:23 AM
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
07-14-2005, 10:41 AM
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
07-15-2005, 08:28 AM
Absolutely agree with you on all points. :yes

It will be fixed in the KB entry.

Great...:thumb
Keep it up..

Ken Puls
07-15-2005, 09:36 AM
Just for reference... Check if directory or file exists is now updated in the KB.