Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Pathname
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Pathname
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Pathname
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup dynamic pathname | Excel Discussion (Misc queries) | |||
Using a dynamic pathname in VLOOKUP | Excel Worksheet Functions | |||
Filecopy and UNC-pathname | Excel Programming | |||
Extract Workbook name from pathname ? | Excel Programming | |||
LoadPicture Pathname or no Pathname | Excel Programming |