[SOLVED] Invalid Pathname

I have created an application which searches for network folder contents and
poulates a list on an excel spreadsheet with hyperlinks to he network files.
In order to determine the file locations the user must enter a valid pathname
into an inputbox.

The application works fine as long as the path is either valid or the user
selects 'cancel' from the inputbox to exit the macro. However, if a typo is
made and a completely invalid path entered then the macro just hangs excel
(excel 2000) or starts reading files from C:\Documents and Settings\......

Can anyone suggest how best to check if path is valid? If it is known to be
invalid then I can easily exit the macro with a message displayed to the user.

The part code surrently used is:

sf1 = InputBox("Enter the correct path for specifications", "Specification
Folder Location")
If sf1 = "" Then Exit Sub

'Reset search parameters
sf2 = MsgBox("Do you want to include sub-folders in the search?", vbYesNo +
vbExclamation)
With Application.FileSearch
.NewSearch
.LookIn = sf1
If sf2 = vbYes Then
.SearchSubFolders = True
Else: .SearchSubFolders = False
End If
.FileType = msoFileTypeAllFiles
.Execute
'To prevent misuse and macro errors
If .FoundFiles.Count > 1000 Then
MsgBox "You are about to add over 1000 file references." & vbCrLf _
& vbCrLf & "This will create errors within the macros in this
workbook" & vbCrLf _
& vbCrLf & "The procedure will now cancel"
GoTo Line3
ElseIf .FoundFiles.Count = 0 Then
MsgBox "No files found." & vbCrLf & vbCrLf & _
"Check that specifications are located in the correct folder and
correct filepath has been used"
GoTo Line3
End If
End With

Here is a function that you can use to test the filename

'-----------------------------------------------------------------
Function ValidFilename(Path As String) As Boolean
'-----------------------------------------------------------------
Dim iFile As Long
On Error Resume Next
iFile = FreeFile()
ValidFilename = Not Dir(Path) = ""
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"pumpbhoy" <[email protected]> wrote in message
news:[email protected]...
> I have created an application which searches for network folder contents
and
> poulates a list on an excel spreadsheet with hyperlinks to he network
files.
> In order to determine the file locations the user must enter a valid
pathname
> into an inputbox.
>
> The application works fine as long as the path is either valid or the user
> selects 'cancel' from the inputbox to exit the macro. However, if a typo
is
> made and a completely invalid path entered then the macro just hangs excel
> (excel 2000) or starts reading files from C:\Documents and Settings\......
>
> Can anyone suggest how best to check if path is valid? If it is known to
be
> invalid then I can easily exit the macro with a message displayed to the
user.
>
> The part code surrently used is:
>
> sf1 = InputBox("Enter the correct path for specifications", "Specification
> Folder Location")
> If sf1 = "" Then Exit Sub
>
> 'Reset search parameters
> sf2 = MsgBox("Do you want to include sub-folders in the search?", vbYesNo
+
> vbExclamation)
> With Application.FileSearch
> .NewSearch
> .LookIn = sf1
> If sf2 = vbYes Then
> .SearchSubFolders = True
> Else: .SearchSubFolders = False
> End If
> .FileType = msoFileTypeAllFiles
> .Execute
> 'To prevent misuse and macro errors
> If .FoundFiles.Count > 1000 Then
> MsgBox "You are about to add over 1000 file references." & vbCrLf
_
> & vbCrLf & "This will create errors within the macros in this
> workbook" & vbCrLf _
> & vbCrLf & "The procedure will now cancel"
> GoTo Line3
> ElseIf .FoundFiles.Count = 0 Then
> MsgBox "No files found." & vbCrLf & vbCrLf & _
> "Check that specifications are located in the correct folder and
> correct filepath has been used"
> GoTo Line3
> End If
> End With

Thanks Bob,

I'm a bit of a novice I'm afraid and not sure how exactly to apply the code
you provided. As it happens I have managed to fix it by including the
following:

Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(sf1) Then
MsgBox "Invalid path entered. The folder does not exist." & vbCrLf &
vbCrLf _
& "Please check and try again."
Exit Sub
End If


"Bob Phillips" wrote:

> Here is a function that you can use to test the filename
>
> '-----------------------------------------------------------------
> Function ValidFilename(Path As String) As Boolean
> '-----------------------------------------------------------------
> Dim iFile As Long
> On Error Resume Next
> iFile = FreeFile()
> ValidFilename = Not Dir(Path) = ""
> End Function
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "pumpbhoy" <[email protected]> wrote in message
> news:[email protected]...
> > I have created an application which searches for network folder contents
> and
> > poulates a list on an excel spreadsheet with hyperlinks to he network
> files.
> > In order to determine the file locations the user must enter a valid
> pathname
> > into an inputbox.
> >
> > The application works fine as long as the path is either valid or the user
> > selects 'cancel' from the inputbox to exit the macro. However, if a typo
> is
> > made and a completely invalid path entered then the macro just hangs excel
> > (excel 2000) or starts reading files from C:\Documents and Settings\......
> >
> > Can anyone suggest how best to check if path is valid? If it is known to
> be
> > invalid then I can easily exit the macro with a message displayed to the
> user.
> >
> > The part code surrently used is:
> >
> > sf1 = InputBox("Enter the correct path for specifications", "Specification
> > Folder Location")
> > If sf1 = "" Then Exit Sub
> >
> > 'Reset search parameters
> > sf2 = MsgBox("Do you want to include sub-folders in the search?", vbYesNo
> +
> > vbExclamation)
> > With Application.FileSearch
> > .NewSearch
> > .LookIn = sf1
> > If sf2 = vbYes Then
> > .SearchSubFolders = True
> > Else: .SearchSubFolders = False
> > End If
> > .FileType = msoFileTypeAllFiles
> > .Execute
> > 'To prevent misuse and macro errors
> > If .FoundFiles.Count > 1000 Then
> > MsgBox "You are about to add over 1000 file references." & vbCrLf
> _
> > & vbCrLf & "This will create errors within the macros in this
> > workbook" & vbCrLf _
> > & vbCrLf & "The procedure will now cancel"
> > GoTo Line3
> > ElseIf .FoundFiles.Count = 0 Then
> > MsgBox "No files found." & vbCrLf & vbCrLf & _
> > "Check that specifications are located in the correct folder and
> > correct filepath has been used"
> > GoTo Line3
> > End If
> > End With
>
>
>

Yes but that is deficient in that it just checks that the folder exists.
Invalid characters could still be entered in the filename, which is what my
function handles.


You could use the code like so

Do
sf1 = InputBox("Enter the correct path for specifications",
"Specification Folder Location")
If sf1 = "" Then Exit Sub
If Not ValidFilename(sf1) then
Msgbox "Filename is invalid"
fError = True
Else
fError = False
End If
Loop Until Not fError

which forces them to re-submit, ot

sf1 = InputBox("Enter the correct path for specifications",
"Specification Folder Location")
If sf1 = "" Then Exit Sub
If Not ValidFilename(sf1) then
Msgbox "Filename is invalid"
Exit Sub
End If

which quits on them

--

HTH

RP
(remove nothere from the email address if mailing direct)


"pumpbhoy" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob,
>
> I'm a bit of a novice I'm afraid and not sure how exactly to apply the
code
> you provided. As it happens I have managed to fix it by including the
> following:
>
> Set fs = CreateObject("Scripting.FileSystemObject")
> If Not fs.FolderExists(sf1) Then
> MsgBox "Invalid path entered. The folder does not exist." & vbCrLf &
> vbCrLf _
> & "Please check and try again."
> Exit Sub
> End If
>
>
> "Bob Phillips" wrote:
>
> > Here is a function that you can use to test the filename
> >
> > '-----------------------------------------------------------------
> > Function ValidFilename(Path As String) As Boolean
> > '-----------------------------------------------------------------
> > Dim iFile As Long
> > On Error Resume Next
> > iFile = FreeFile()
> > ValidFilename = Not Dir(Path) = ""
> > End Function
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "pumpbhoy" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have created an application which searches for network folder
contents
> > and
> > > poulates a list on an excel spreadsheet with hyperlinks to he network
> > files.
> > > In order to determine the file locations the user must enter a valid
> > pathname
> > > into an inputbox.
> > >
> > > The application works fine as long as the path is either valid or the
user
> > > selects 'cancel' from the inputbox to exit the macro. However, if a
typo
> > is
> > > made and a completely invalid path entered then the macro just hangs
excel
> > > (excel 2000) or starts reading files from C:\Documents and
Settings\......
> > >
> > > Can anyone suggest how best to check if path is valid? If it is known
to
> > be
> > > invalid then I can easily exit the macro with a message displayed to
the
> > user.
> > >
> > > The part code surrently used is:
> > >
> > > sf1 = InputBox("Enter the correct path for specifications",
"Specification
> > > Folder Location")
> > > If sf1 = "" Then Exit Sub
> > >
> > > 'Reset search parameters
> > > sf2 = MsgBox("Do you want to include sub-folders in the search?",
vbYesNo
> > +
> > > vbExclamation)
> > > With Application.FileSearch
> > > .NewSearch
> > > .LookIn = sf1
> > > If sf2 = vbYes Then
> > > .SearchSubFolders = True
> > > Else: .SearchSubFolders = False
> > > End If
> > > .FileType = msoFileTypeAllFiles
> > > .Execute
> > > 'To prevent misuse and macro errors
> > > If .FoundFiles.Count > 1000 Then
> > > MsgBox "You are about to add over 1000 file references." &
vbCrLf
> > _
> > > & vbCrLf & "This will create errors within the macros in this
> > > workbook" & vbCrLf _
> > > & vbCrLf & "The procedure will now cancel"
> > > GoTo Line3
> > > ElseIf .FoundFiles.Count = 0 Then
> > > MsgBox "No files found." & vbCrLf & vbCrLf & _
> > > "Check that specifications are located in the correct folder
and
> > > correct filepath has been used"
> > > GoTo Line3
> > > End If
> > > End With
> >
> >
> >